We all know that Excel is full of tons of powerful functions, formula libraries, and interesting tables galleries, which makes it one of the most useful tools for business analysts. I believe that, among Excel’s great secrets, there are some that are easy to use and that can add value to your work. Let’s see these functions, which I like to call ripe fruit, which include Sparklines, Conditional Format, Cutters and some simple but useful formulas that may not have been discovered.
Sparklines, presented for the first time in Excel 2010, are tables that provide a simple visualization of the representation of trends through a row of your data, in a simple cell of the spreadsheet. Sparklines offers excellent space savings on jammed spreadsheets and can be quite intuitive for the amount of space used. This feature is incredibly fascinating and very easy to use. However, not many analysts take advantage of these small and powerful tables.
To create your own Sparklines, select the range of data and on the ribbon click on Insert and then select the type of Sparklines, Line, Column or Win / Loss. Next, insert the range where you want the Sparkline to be displayed. That’s all there is to do.
The options available in the Conditional Format function allow them to quickly create indicator maps and convert a data table into a continuous spectrum of colors for a deep visual analysis. This is a simple and effective way to explore your data and find interesting patterns.
Below is a map of indicators where the color scale option was applied literally in ten seconds from the same sales data used in the Sparklines example above, to illustrate the large (dark green) and low (white) Sales data through products and time.
To create the map with the help of the Conditional Format, select the raw data and then, in the ribbon below the Main Menu , click on Conditional Format> Color Scale and then select a color scale. You can also adjust the color options by just editing the format rule.
SMALL and BIG functions
While everyone knows MAX and MIN functions, very few take advantage of the BIG and SMALL functions that help them find the first, second or largest (or smallest) value in their data.
With the help of the same sales data as in the examples above, the GRANDE functions allow us to quickly identify the maximum of two products for each month in our data. See the examples below, already finished with the help of INDEX and MATCH, two other super useful functions to analyze.
There are many constants in corporate life and cleaning and organizing data is one of them. Removing Duplicates, is a feature presented in Excel 2007, remains one of my favorites. It is very easy to use and solves an important problem that we all face, duplicate data.
Just select the data and then, on the ribbon below Data , click on the Duplicate Remover button , and see how Excel cleans your data. It’s that easy.
Imagine the Cutters as visual filters that help to reduce the subsets of data, and visualize them (even in raw data formats or through a connected table). The Cutters were presented in Excel 2010 and became even better in Excel 2013.
For example, we can create an interactive graphic of trends quickly with the help of the cutters. When presenting the sales data, we can now alternate between different products with the help of the same table.
To add a cutter in your Excel 2013 tables, select the range of data you want to use as a filter. For the example above, we choose the Product column. After everything is ready!
To play with the interactive table and see more of this technique, you can Download Microsoft Excel.