Next-Level Excel: Efficiently Move Beyond Arithmetic
Updated: Nov 19, 2020
Every small business should have at least one intermediate Excel practitioner on staff. As intimidating as “intermediate Excel practitioner” sounds, there are actually only a handful of skills needed to get anyone to that level. These skills remove considerable time from spreadsheet-based processes. They can also be a valuable step towards working with more advanced business intelligence platforms.
Here are some suggestions for anyone looking to make that jump.
Know three formulas that drive Excel analysis
The vast majority of Excel users only know how to use it to perform arithmetic. Getting to the next level requires learning more complex formulas, but of the vast number of tasks that formulas can perform, there are only three that will drive at least 80% of the value in business analysis:
Those three formulas will look at data tables to find a number (VLOOKUP), add up certain numbers but not others (SUMIF), and do something different based on what the formula finds (IF). While there is not enough space in this post to dig into the details, there are plenty of videos and articles online to be found with a simple browser search.
Understand pivot tables and charts
The pivot table and the pivot chart are instant “slice and dice” tools that can turn information into actionable insight. They allow data to be grouped (e.g., sum of sales) and displayed by categories in both rows (e.g., product type) and columns (e.g., month). In seconds, one can demonstrate trends to the bosses.
This is another spot where online videos and articles can get a user up to speed. With an interesting dataset to play with, it should only take an hour or two to master the basics.
Learn the art of Excel
Understand that producing strong Excel analysis is an art as much as it is a science
There are some “laws” that are invariably true about building effective spreadsheet analyses. For example, data inputs and report outputs should be separated into different tabs. Those input tabs should hold source data in tables (e.g., sales transactions). Reports, which are in a different tab or tabs, should pull from the data inputs (use the formulas above!). This makes updating the reports easy. Just update the data in the input tabs and the reports will update themselves.
But much of it comes down to creative problem solving. There may be several ways to get to point B, and different situations may call for different paths. There is no need to be afraid. Try strange things because that is what the experts do. Find opportunities to continue trying new ways of organizing logic. More opportunities will give more comfort with the creative process.
Learn Power Query
Power Query lets one automate rote tasks performed every time an old workbook is updated into a new version. Imagine updating monthly revenue reports by hitting a refresh button.
Users can connect directly into either their own Excel tables, or external data sources (like Salesforce objects, database tables, etc.). Without having to write any code, the user can produce a query that drops the results into an Excel table (among other options). When they want to update that data, they press refresh, and the table pulls the latest info from the source.
Once again, there are plenty of online resources available to get started. And with no new code to be learned – the interface looks like regular Excel – a user can be running in an hour or two.
Moving to the next level in Excel is good for individuals and their organizations. Individuals advance themselves by becoming more productive. The firm shares the benefit of that productivity, so firms should be keen to give people room to learn. Formal training can be an accelerator, but there are enough free resources online that a curious individual can “train up” in just a few weeks while still doing their job.
For anyone looking to become more useful – or has an employee who would benefit – try the path above for an efficient way to get there. And ping us! We always love to hear feedback about the journey.