Advanced Excel, to most people, is about knowing more formulas and worksheet functions. The more you know, the better you become. However, in this portal, we are simply not interested to know just more formulas and worksheet functions. We are interested to know how these worksheet functions and formulas are able to help clean up data downloaded into Excel, analyse data and prepare reports that we can use on a daily or regular basis. Excel should be a tool to help us work work better and more efficiently, not just a worksheet with boxes to collect and present data.
Most of the Excel formulas (commonly known as worksheet functions) are great for cleaning up data and they work hand in hand with some of the functions in Excel. Common functions for cleaning up data are TRIM, MID, SEARCH, etc. Merging data usually uses VLOOKUP and for complex ones, you can use a combination of INDEX and MATCH. In Excel 2016, Get and Transform, a new section adopted from Power Query, is capable of replacing some of the common formulas used to clean up data. Get and Transform is really powerful. It can even transpose part of the data and make it into a nice database for PivotTable. It can even retrace back steps taken without worrying that you can’t UNDO certain steps. Get and Transform can even merge 2 record sets into one (great substitue for VLOOKUP) and append data sets (consolidation) for analysis purposes, especially budgeting and cost allocation.
With the data cleaned, you can make use of PivotTable for basic analysis. Finding TOP 5, SORTING the records based on performance are some of the analysis you can perform with PivotTable. Using PivotTable, there is no straightforward way to find variance. But PowerPivot can. Both PivotTable and PowerPivot look similar but they are different. PowerPivot can calculate variance, present YTD numbers and many more complex calculations using DAX formulas which are not available in PivotTable.
Reporting using PivotTable used to be very tedious as there are a lot of manual copying, pasting and referencing. With the introduction of slicer, the steps can be shortened or ommitted. Slicer and timeline (for Excel 2013 and later) , you can filter multiple PivotTables all at one go. Combined Slicer and Timeline with PowerPivot and Pivot Charts, you will get an interactive Dashboard that is easy to maintain and beautifully designed.