The Power of Excel
I recently came across a LinkedIn post titled “Excel is Dead”. The post was by Lance Rubin a great financial modeler and one of many great finance professionals I follow on LinkedIn. If you are interested, you can click on the link to read his article Excel is Dead. In addition to the article, a podcast was created that brings people on both sides of the argument together for a debate which can be found here.
I have done a lot of thinking about modeling, spreadsheets, data analysis, and Excel since reading the article, and have concluded that Excel is not dead but alive and thriving. It is one of the most powerful and most used finance software ever created. Even though it is alive and well, many other tools have been developed that are better than Excel for many tasks we once used Excel for and today’s fast-changing business environment requires the use of many tools to be successful. The rest of this article will focus on Excel and demonstrate why it is still the number one tool used by FP&A professionals everywhere. Instead of boring you with my opinions on Excel we will do a couple of exercises that demonstrate Excel’s ubiquity.
Excel’s Ubiquity
For the first exercise, I conducted a Google search for five of the top spreadsheet software programs available with the results below:
Microsoft Excel – 3,050,000 results
Apache OpenOffice Calc – 1,210,000 results
LibreOffice Calc – 86,000 results
Google Sheets – 2,760,000 results
Zoho Sheets – 252,000 results
Next, I conducted a video search on YouTube and found that Microsoft Excel has about double the number of videos as the other four spreadsheet programs combined. If you continue to search the web you will find substantially more training available, books written, and companies dedicated to supporting and teaching Excel than all other spreadsheet software combined. The above exercises make it obvious that Excel is a lot more talked about on the web than other spreadsheet tools but is it really the tool of choice for FP&A analysts? To answer this question, went out to a popular job board site indeed.com typed in FP&A and looked at the first 20 or so job descriptions and provided some of the requirements regarding spreadsheets and modeling below:
Proficient in Microsoft Office, especially in Excel
Proficient ERP experience (SAP preferred), Excel and BI tools (Business Objects preferred) required. BPC Script Logic Knowledge is a plus
Expert level of proficiency in Microsoft Excel
Strong Excel skills required
Excellent MS Excel and MS PowerPoint skills with demonstrated experience creating financial models that incorporate the use of database manipulation
Advanced skills in Excel and other financial modeling tools
Advanced Excel skills
Experience with Excel/Access / Hyperion Planning / Essbase a plus
Strong Excel and Systems experience
In-depth knowledge of financial software as well as Microsoft Office (honestly, Excel should get your blood pumpin’)
The last bullet above was my favorite Excel should get your blood pumpin as this is how I feel about Excel, data, data analysis, and BI tools. After reviewing 20-job posts I found that all but 2 of the jobs required experience using Excel and most required advanced Excel skills. What is interesting to note as you start to review finance leadership positions the ability to use Excel is often not mentioned in the posting as a requirement.
What this tells us is Excel is viewed as a technical skill and is required to get started in FP&A but as you move up in your career other skills such as business partnering, strategic thinking, and leadership become much more important. As you begin your career you must demonstrate proficiency in the technical skills but to advance you career you must demonstrate ability to use soft skills.
I hope the above exercises have made it abundantly clear Excel is not dead nor going anywhere, in fact, it is an entry level requirement to work in FP&A. However, if you do manage to get a job that does not require Excel skills you will still have to be able to model, analyze, and visualize data, and you will use some form of spreadsheet and/or data visualization software to accomplish the task. Before moving forward, I want to again emphasize Excel is not always the right solution or tool. For example, when analyzing very large data sets, or rolling up enterprise wide financial planning documents tools other than Excel are often the best choice. These software tools will be discussed in later posts so back to Excel. Excel is the most powerful spreadsheet software tool in the world and so much more than that. As one learns Excel one must learn both how to design spreadsheets and the functionality of Excel itself.
Proper Design
Proper design modeling is a requirement if one hopes to be a proficient user of Excel. I have seen users who have great Excel skills but have no idea how to design a spreadsheet and vice versa. If you have ever had to spend a week trying to understand someone else’s Excel work you will understand why learning proper design techniques is discussed before learning Excel’s functionality.
I have seen many users who have great Excel skills but have no idea how to design a spreadsheet and I would much rather work with a well-designed file by a person who has minimal understanding of Excel functions than someone who has poor design habits but is a wizard at creating complex files. If you have ever had to spend a week trying to understand someone else’s Excel work you will understand why design is critical.
I am not going to spend much time providing instructions on design but will highlight a few basic design tips:
NEVER HARDCODE numbers in formulas – This rule is first because it is the rule that is broken most often and causes the most pain for people who inherit another person’s work.
Include overview tab – Including documentation explaining the workbook and providing a table of contents makes it much easier for others to follow.
Label spreadsheet tabs and assumptions – using clear and concise labels for all tabs and assumptions make a workbook easier to follow
The above three listed rules are just a starting place and by no means comprehensive. Will recommend for anyone struggling with proper spreadsheet to spend some time reading a few articles on proper spreadsheet design and would even consider picking up a good modeling book on the subject. The next section will focus on the power of Excel and some of the great functionality available in Excel.
Excel Functionality
Over the years Excel has added a lot of functionality and today it is so much more than just a spreadsheet tool. Most people do not even use or realize many of the tools that are built into Excel. I once came across an article that talked about the five languages of Excel. The term” language” is used loosely as these are not programming languages in the traditional sense, but each of these five areas has a unique syntax one must learn. Three of the five are tools built into Excel that go beyond what we would traditionally think of as spreadsheet software and greatly enhance what one can accomplish with Excel. The other two are the bread and butter of Excel, Functions and Custom Formatting which is very helpful in making documents readable. The five “languages” listed below with a brief discussion of each.
Functions
Power Query & M Script
Power Pivot & DAX
Custom Formatting
VBA
Functions
Make no mistake about it, the bread butter of any spreadsheet is Functions and Excel has hundreds of built-in functions and the ability to create even more custom functions. When first learning to use Excel most of your time will be spent learning functions and the simple syntax used to write these functions. Even as one advances in Excel a lot of time will still be spent learning functions and how to combine functions to accomplish complex tasks. Whole books have been written on nothing but Excel functions and one of my favorites is John Walkenbach’s Excel 2013 Formulas. As you learn the art of writing formulas in Excel, I recommend picking up a few books on the subject and John’s should be at the top of the list.
Power Query
Power Query is a recent tool added to Excel in the 2010 version and is an ETL – Extract Transform Load tool. Power Query is a great tool for pulling data into Excel from other sources and then transforming and cleaning the data before loading it into Power Pivot or an Excel spreadsheet. Where Power Query shines is automating those manual cleansing tasks that every FP&A Analyst has performed prior to producing a report. Power Query allows you to pull data from multiple data sources into Excel and transform it into the format you want before working with it in your Excel Spreadsheet.
The software has a great graphical user interface that can allow you to do most of the transformations you want without ever having to learn the code used to perform the tasks. If you truly want to harness the power of Power Query you will need to learn M script which is the scripting language Power Query uses. In my opinion this tool is so powerful that I recommend learning it after learning how to use the basic functions in Excel.
This tool has saved me many hours cleaning data and is much easier to learn than VBA coding due to the easy to use graphical interface built into Power Query. The next tool on my list Power Pivot is probably my favorite tool in Excel.
Power Pivot
Power Pivot is the sister program of Power BI and is part of Microsoft’s business intelligence suite. Power Pivot is one of my favorite add-ins to Microsoft Excel as It allows you to create data models combining multiple sources together without having to use any of the traditional lookup methods so common in Excel. What this means is instead of having to create one huge data source and then creating a pivot table you can store multiple tables, link them all together and perform complex calculations on all of them.
I only learned about Power Pivot a year ago and the stuff I have been able to design has helped me gain a strong reputation at work as a data guy. My company has multiple systems and no standard BI tool due to multiple acquisitions over the years so being able to create my own dashboards and reports combining multiple data sources has been invaluable. The first report I created became so valuable I had people in the company contacting me to provide them with reporting that I had never worked with.
As you learn to harness the power of Power Pivot you will need to learn about data modeling and DAX. DAX stands for Data Analysis Expressions and is the formula language that is used to perform calculations on your data. What you will find is combining the Power Query tool with Power Pivot will allow you to create complex dashboards that are easy to refresh and update in minutes.
Custom Formatting
The next “language” on my list is custom formatting. Excel allows one to write custom number formats, but to do this one has to learn a syntax completely different than the syntax for functions, Power Query, Power Pivot or VBA. Where this is helpful is in creating aesthetically pleasing documents. Anyone who has worked in FP&A has had to create a document where we want to display a number in millions and this is the kind of task that custom formatting is designed to accomplish. No more changing the number 1,000,0000 to 1,000 or even 1 in excel manually. As you learn custom number formatting you will find it allows you to format your numbers almost any way imaginable.
VBA
The last of the five “languages” of excel is Macros and VBA. VBA stands for Visual Basic Application and is a programming language used throughout Microsoft Office. VBA allows one to write custom programs to extend the power of Excel. VBA has a wide range of uses from recording simple macros to writing complex add-ins that enhance the functionality of excel.
When it comes to learning VBA, I recommend learning how to write basic macros to automate repetitive tasks at a minimum. However, for those who want to harness the full power of Excel I recommend learning the VBA language as it will unlock the ability to create truly amazing software models.
Conclusion
In summary Excel is here to stay and learning to become an expert in Excel is a requirement to work in FP&A and all of Finance. If you are early in your career I highly recommend spending time learning good design and modeling practices and then learning how to unlock the power of Excel. As you learn to unlock the power of Excel, take time to learn Power Query, and Power Pivot as they will allow you to become a master at manipulating and analyzing data. As my final parting wisdom, I hope “Excel gets your blood pumpin” because you can accomplish amazing things using Excel and set yourself apart in your career by providing the kind of analysis and insight that drivers better financial results.
