If you have used the MAX and MIN functions in Excel, then you know they return a single value representing the highest and the lowest value in a given range. LARGE and SMALL functions on the other hand allow you to specify whether you want the 2nd, 3rd, 4th, largest/smallest value in the same range. As such, you have to specify the range of values, as well as the ‘Kth’ element, where ‘K’ is any number 1, 2, 3, and so on. The ‘k’ element tells Excel what top/bottom values to return from the specified range. Continue reading “Excel Dashboards: Show top/bottom values”
Creating an Excel Dashboard for data visualization is one of the key skills analysts in vast majority of organizations require. The good news is that Microsoft Excel has continued to make improvements that allow the process and available visualizations (charts) easy to generate. For instance, with Excel 2016, you have a Waterfall chart as a standard chart. With Excel 2013 and prior versions, creating a waterfall chart would require some tweaking, but yes, it is achievable (follow the link to learn the 4 steps). I will discuss some key concepts among the undermentioned. You can download a copy of the file used in this example at the bottom of this article.
- Custom calculations in Pivot Tables
- Adding interaction to dashboards with slicers
- Custom number formats and conditional formatting
- Using shapes for a prettier excel dashboard
- Applications of VLOOKUP + MATCH and named ranges
Safaricom, the most admired brand in the telecommunication industry in Kenya has seen yet a more profitable year 2015 with total revenue having a YoY growth of +12.9% to 163.4bn, whereas Free Cash Flow (FCF) grew by +21.3% to 27.5bn (Source: Safaricom Financial Results). What fascinated me though is the use of the waterfall chart to visualize both the revenues and the FCF. But have you ever wondered how to create this chart in Excel? This post will give you the 4 steps you need to create a waterfall chart in Excel 2013.
If you are involved in management reporting, the statement above resonates with you. You need an appealing, interactive dashboard that articulately gives you the insights you need to make a decision.