Top Ten Ways to Analyze Your Data

When working with really large data lists in your Excel worksheets, it’s all too easy to lose sight of the developing trends in that data indicating new business opportunities, let alone warning of possible dangers. Fortunately, Excel 2019 offers several easy-to-use features designed to help you summarize and visualize directions and movements inherent in the vast quantities of data you deal with.

Here are my top ten favorite features in Excel 2019 for evaluating the data and drawing out the prevailing trends and vital indicators:

10. Pivot Tables. The PivotTable feature offers one of the most efficient and flexible ways to summarize and analyze large amounts of data in the data lists you maintain or import into your Excel worksheets. Pivot tables are extremely easy to create as well as to modify.

    Pivot tables provide you with an easy way to cross-tabulate your data using any of the most common statistical functions (Sum, Count, Average, Max, Min, and so forth). They also provide you with the means for adding “filtering” fields from the data list or model that enable you to instantly refine the summarized data.

    See Chapter 9 for more on creating and using pivot tables to analyze large amounts of data.

9. Pivot Charts. As the visual companion to pivot tables, Excel’s PivotChart feature allows you to graph the data summarized in your pivot tables using any of the types of charts supported by Excel 2019. As with pivot tables, pivot charts are highly flexible, enabling you to filter the graphed data on the fly and instantly see the effect in your pivot chart.

   See Chapter 9 for more on creating and using pivot charts to graphically highlight the data summarized by your pivot tables.

8. Slicers. Slicers provide a highly efficient way to refine the summarized data in your pivot tables using criteria from as many fields as needed. Moreover, as graphic objects, you can easily position slicers so that they’re displayed just as you need them on the screen and quickly modify their criteria.

   See Chapter 9 for more on creating and using slicers to quickly refine the data displayed in your pivot tables using multiple criteria from several fields.

7. Timelines. Timelines are slicers specifically for the date fields used in your pivot tables. They enable you to filter the summarized table data using specific time-related criteria based on any of a number of time intervals (days, months, quarters, or years). Timelines make it possible to see the summaries of your pivot table data for any time period you care to track.

   See Chapter 9 for more on creating and using timelines to refine the data displayed in your pivot tables using time-sensitive criteria from date fields in your pivot tables.

6. AutoFilters. Excel offers a variety of AutoFilter controls that you can use to instantly refine the data you track in the data lists you maintain in your Excel worksheets. As soon as you format your data list as an Excel table (Alt+HT), Excel automatically adds AutoFilter buttons to the header row of the data list containing the list’s field names.

   At the most basic level, you can filter the data list by setting the criteria to particular entries in certain fields of the data list. In addition, Excel offers a set of Text, Date, and Number Filters (depending upon the type of entries in a field) that make it easy to refine the list using a wide variety of field-type specific criteria. If none of these readymade filters will do, you can always use the Custom Filter option in order to set up a range for the criteria that can include multiple fields using And or Or conditions.

   See Chapter 11 for more on using the various AutoFilter controls to refine the data displayed in your Excel data list.

5. Advanced Filter. In addition to filtering the data list in place as do the AutoFilter options attached to the specific fields of an Excel data list, the Advanced Filter feature (available by selecting Data ⇒ Advanced on the Ribbon or pressing Alt+AQ) also enables you to copy the records from the data list that meet your criteria to a new location in the worksheet.

   Before you use the Advanced Filter feature, you need to set up a Criteria Range somewhere on the worksheet outside (usually to the side) of the data list you’re going to filter. This Criteria Range consists of a copy of the row of field names from the top row of the data list. Beneath the particular fields’ names that are to be used in the filtering, you place the actual criteria. So, for example, if you wanted to work only with records where the zip code is 94102, you enter 94102 in the blank cell right below the field name Zip Code. So too, if you want to work with records where the zip code is 94102 or 94103, you enter 94103 in the blank cell below the one containing 94102. When filtering the data list on criteria in numeric fields, you can use number operators such as =, >, <, <>, and so forth. Just keep in mind that criteria under different fields in the same row of the criteria range create an And condition (as in City is Chicago and Order Total is >100.00) just as criteria in different rows of the criteria range create an Or condition (as in City is Chicago or New York) and that the Criteria Range needs to include the row with the copied data list field names as well as all the rows that contain criteria you want used in filtering the data list.

   The Advanced Filter feature is great when you need to work with a subset of the data list that meets certain criteria (such as the records in an orders data list where the order total greater than or equal to $500.00) without affecting the original data list itself.

4. Goal Seeking. Goal Seeking provides a simple way to analyze simple goals that need to be set in order to meet important milestones in your business. Goal Seeking works by attempting to find the particular value you designate for a Set Cell by changing the value that’s entered in the so-called Changing Cell.

   So, for example, with Goal Seeking, you can immediately see how much you have to make in gross sales in order to meet a particular net sales amount. You do this by designating the cell with gross sales as the Changing Cell and the cell with the net sales as the Set Cell in a worksheet table containing the formulas the calculate the net sales by deducting all the expected costs of doing business from the gross sales.

   See Chapter 8 for more on forecasting with the Goal Seeking feature.

3. Scenario Manager. Excel’s Scenario Manager takes what-if data analysis to a whole new level by making it easy for you to create a whole series of possible financial scenarios, such as Most Likely, Best, and Worst case, based on various assumptions that indicate different levels of confidence. Perhaps the best part of the Scenario Manager is the Summary feature that enables you to create a summary table or pivot table on a separate worksheet that immediately shows the effect of the various scenario assumptions on the bottom line side by side.

   See Chapter 8 for more on using the Scenario Manager.

2. Recommended Charts. Never underestimate the power of the chart to help you immediately see trends that would otherwise go unnoticed in your worksheet data. And, a lot of times, choosing just the right type of chart for the type of data you’re graphing is the key to bringing a developing trend to light. That’s where Excel’s Recommended Charts feature (Insert ⇒ Recommended Charts or Alt+NR) comes in so handy.

   When you choose this command, Excel previews the range of data currently selected in your worksheet, depicting the data in several different chart types that the program assumes best suit that data. You can then scroll through these chart previews to see which one does the best job in telling the data’s story. If none of Excel’s recommended charts seem to do the trick, you can then switch over to the All Charts tab where you can preview the data in any type of chart that Excel 2019 supports.

   See Chapter 10 for more on creating great-looking charts using the Recommended Charts feature.

1. Insights. The new Insights feature in Excel 2019 enables you to instantly build pivot charts and regular charts that highlight otherwise unnoticed aspects of your data.

   To use the Insights feature, you simply position the cell cursor in one of the cells in your data list before you select Data ⇒ Insights on the Ribbon or press Alt+NDI. Excel then opens an Insights task pane that contains thumbnails of pivot charts and sometimes regular charts based on a particular aspect of the data.

   Then, all that remains to do is for you to create any or all of the suggested charts appearing in the Insights task pane is to click the Insert PivotChart or Insert Chart link that appears in the lower-left corner of its thumbnail. Excel 2019 then creates an embedded chart on a new worksheet. In the case of a pivot chart, the program also creates a supporting pivot table on the new worksheet

   See Chapter 9 for more on creating and using pivot tables and pivot charts in a worksheet.