Microsoft Excel — 4 Techniques to Glean Insight From a Report of Data

Quickly Generate Visual Insight and Summaries

Data to Useful Insight

A common report in business is a “trend” report, which typically depicts an item we are measuring — product sales, projects, employee data, etc. — spread across a number of months.

This is used to spot trends, etc. that are not possible to see on a comparative report.

However, the problem with a trend report of data is that it’s a sea of numbers without some summary and visuals applied to it.

Here are four techniques I use — illustrated on this report. If you want the Excel file, a download link is available here.

1 — Set Up A Variable Field to Modify View Based on User Preference

At Top of Report — User Enters Amount that Drives Analysis

• Once that field (I range name it “cutoff”) is set, the remaining techniques drive off of that amount.

2 — Above Report — Add Summary Table and a “Flash” Chart

Summary Table and “Flash” Chart Above Data Table

This provides a quick, relevant recap of the entire data population for the user.

Summary Analysis Table

Data Summary Table — Quick Data Assessment

The following used to prepare this summary table:

• The “cutoff” amount drives the description column amounts. The titles are completed using “Custom formatting” under cell formatting options.
• COUNTIF function to Count the values in each category:

=COUNTIF(ProductData,”>”&cutoff)

• SUMIF function to summarize the values in each category:

=SUMIF(ProductData,”>”&cutoff)

• The % Count and % Sum are straightforward calculations.

Summary Chart

Summary Chart from separate worksheet calc

• Chart is generated for just the Sum of $’s and the total %’s.
• The chart data is dynamic since titles drive off of the “cutoff” amount.

3 — The Data Table is Conditionally Formatted Based on the “Cutoff” Amount

Data Table is conditionally formatted based on “Cutoff” amount entered

Here are the conditional formatting criteria to configure this:

Conditional Formatting settings for highlighting

4 — Create a Summary Recap for Each Month

Below each month, a brief recap of relevant data is provided. This is similar to the overall data table at the top of the table, but calculations are for each month.

Summary Recaps for each month

About Don

“It’s time for DIFFERENT”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat