Today’s post on Excel was written by Purna Duggirala president of chandoo.org.
We all know that Excel is packed with tons of powerful features, libraries of formulas and galleries of interesting charts, making it one of the most useful tools for business analysts. I believe that, among the great secrets of Excel, there are several that are easy to use and quickly add value to your work. Let’s take a look at these features—what I like to call the low-hanging fruit— including Sparklines, Conditional Formatting, Slicers and a few simple but useful formulas you may not have noticed.
Sparklines, first introduced in Excel 2010, are charts that provide simple visualization representations of trends across a row of your data—in a single worksheet cell. Sparklines offer excellent real-estate savings on crowded dashboard worksheets and can be extremely insightful for the amount of space used. This feature is unbelievably cool and ridiculously simple to use. And yet, not many analysts capitalize on these powerful tiny charts.
To create your own Sparklines, select the data range and on the ribbon click Insert and then select the Sparklines type—Line, Column or Win/Loss. Next enter the target range where you want the Sparkline displayed. That’s all there is to it.
The options available in the Conditional Formatting feature allow you to quickly create heat maps, turning a table of data into a continuous spectrum of colors for insightful visual analysis. This is a simple and effective way to explore your data and find interesting patterns.
Below is a heat map where the color scale option was applied in literally 10 seconds from the same sales data used in the Sparklines example above to illustrate the high (dark green) and low (white) sales data across products and time.
To create a heat map using Conditional Formatting, select your raw data and then on the ribbon under Home, click Conditional Formatting > Color Scales and then pick a color scale. You can also adjust color scale options by editing the formatting rule.
SMALL and LARGE functions
While everyone knows MAX and MIN functions, very few take advantage of LARGE and SMALL—functions that help you find the first, second or nth largest (or smallest) value in your data.
Using the same sales data from the examples above, the LARGE functions is able to quickly identify the top two products for each month of our data. See the example below, done with the help of INDEX and MATCH, two other super useful functions for analysis.
For more on these formulas, download this workbook: Powerful Excel features.
There are many constants in corporate life and cleaning and organizing data is one of them. Remove Duplicates, a feature introduced in Excel 2007, remains one of my favorites. It is very easy to use and solves an important problem we all face—duplicates in data.
Just select the data and then on the ribbon under Data, click the Remove Duplicates button and watch Excel clean your data. It’s that simple.
Think of Slicers as visual filters. They help you quickly narrow down to a subset of data and visualize it (either in a raw data format or through a connected chart). Slicers were introduced in Excel 2010 and became even better in Excel 2013.
For example, we can quickly create an interactive sales trend chart using slicers. When presenting sales data, we can now easily toggle between the different products using the same chart.
To add a slicer to your charts in Excel 2013, select the data range and on the ribbon click Insert > Slicer, then select the part of your data that you want to use as a filter. For the example above, we chose the Product column. Then you are done!
To play with the interactive chart and see more on this technique, download this workbook: Powerful Excel features.
Join me at the PASS BA Conference 2015
If you find these simple features useful, you are going to love the rest of them.
Come and join me for three days of awesome insights, interactive labs, powerful demonstrations and full-day training on various advanced aspects of Excel at the PASS Business Analytics Conference in Santa Clara, CA April 20-22. There are a ton of sessions on all aspects of Excel, from formulas, to the intricacies of Power BI and other analytics tools.