In high-pressured meetings with Chief Executives, no one would want to be seen as clumsy or slow. And we, as data analysts, are assumed to be fast with navigating spreadsheets or calculating various stats. It is certainly not a good choice to let anxiety take the best of us in front if the C-suite. But, we need not worry. There is something we can do to facilitate our future-self in that meeting room.
Excel Book Author Szilvia Juhasz has interesting tips that can come in handy while presenting analysis on spreadsheet in meetings. Even non-VBA users will find them easy to implement.
So, let’s get started.
1 – The Data
The image shows a sample of data that we might have to present in a meeting. Assume that the months extend to December, 2022. So, essentially, we have data worth 7 years that managers or executives might ask us to calculate various stats from. For example, the Total Revenue expected in 2017 or the Total Operating Income to be incurred in FY 18 (i.e. from July 2017 to June 2018).
Calculating these can take time. And this is solely because of all the data scrolling that we will have to do.
2 – The Trick
Before reading further, make sure the Developer tab is active in Excel’s ribbon. To do this, simply go to File > Options > Customize Ribbon. And now tick the Developer checkbox followed by clicking ‘OK’.
Now go to the Developer tab, and click on Visual Basic. As shown in the image below, we need to insert a Module in our workbook. When ready, just double click on it.
Now we are ready to do some coding! Well, don’t be afraid. Just type in the code shown below. The image shows how it should appear on the screen.
Sub SelectOneYear()
Selection.Resize(, 12).Select
End Sub
Sub OffsetOneYear()
Selection.Offset(, -12).Select
End Sub
The first Macro, ‘SelectOneYear’, simply adds the 11 cells on the right of the current cell to the selection. So, if I have Revenue in Jan-17 selected as my current cell, running this Macro would automatically select all Revenue cells from Jan-17 to Dec-17.
The other Macro, ‘OffsetOneYear’, helps in going back quickly, one year at a time. So, if I have Operating Income in Jun-20 selected as my current cell, running this Macro will take me to the cell containing Operating Income in Jun-19.
3 – Assigning the Macros
Now go back to the worksheet, the one with the data. All we have to do now is draw some objects and assign the Macros to them.
We have drawn arrows and named them according to the Macros that will be assigned to them.
4 – The Test
It’s time to have some fun. Randomly select a cell and press the button assigned the first Macro. Now do the same for the other one. This random testing will makes the user comfortable in using these Macros. Moreover, this will also help us see some limitations of them. For example, we cannot use ‘OffsetOneYear’ if there aren’t 12 cells on the left of our current cell.
5 – Application
We have made some Macros and had fun with them. But what is the point of all this? We need a way to quickly show some stats as we get requests, not just quickly “select” a few cells.
Well, this is exactly where another of Excel’s functionality comes into handy. Whenever we select a few cells, there is a bar at the bottom of the Excel window which comes to live, as shown in the picture on the right. Here we will find our “quick stats” calculated right in the moment. Also, we can right-click on it and select additional stats to display, as per our liking.
6 – Smart Access
As one might have noticed, the data is spread over many columns but our buttons are located under the first few. So, as we would scroll to the right, they will disappear. What’s the use of making these buttons then? Don’t worry, there is a solution. Just use freeze panes and place the buttons in cells which wouldn’t disappear from the screen no matter how far in the spreadsheet we have scrolled.
What’s next?
Go crazy! Try out different things and go into that meeting and show them who is Mr. Smarty-Pants. And do not forget to share this tip! Help your friends and colleagues grow professionally with you.
And do write your comments below on any creative uses of this you might have discovered or your experiences in trying it out.
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
