Some Cool Tricks I’ve Recently Added to my Spreadsheets

Lately, I have been working on this report/dashboard for 2 audiences: the operations manager and the field guys. In developing this tool, I’ve had to incorporate my own tricks.

One, I just recently learned how to make Excel automatically start running a macro when you open up the file. To write the macro, it requires you to deploy Alt-F11 to bring up the VBA editor, to choose This Workbook in the box off to the left, and to right-click and choose View Code. In the editor, you can start typing away and create the code. Once you’re done, this code will NOT show up under the Developer tab; you can only access this piece of the program by using Alt F11.

When automatically running macros upon opening up the file, you  have to consider whether the person wants such actions done or he just wants to look at the last version of the file. So you may need to embed in your automatic code a query to ask if the person wants such actions done or he just wants to see the last version.

Another new feature I’m starting to include is to hide the columns and rows of formulas and then incorporate a conditional formatting to warn me when data may not be included in a table or summary. Normally, when I do a download of data, I usually have formulas off to the left to find certain data, categorize data or do some form of calculations. Then I will do summary of information, based upon the results of those formulas. If there are more data than the formulas, my summary table will be missing information. I could run the formulas all the way down to the bottom of the spreadsheet which is row 1,000,000+, but I don’t want to load up on the size of the spreadsheet. I’m usually already doing a lot of computations and I don’t want to add to the burden needlessly. So I just run the formulas down to a certain number of rows, hoping I cover all of the data that could come in the future. If data should come in larger than anticipated, my conditional formatting will highlight in red (or some other color) to show which rows may not be incorporated into the summary. I’ll see how this works.

Yesterday I showed my work in progress and the response was positive, especially on my visual dashboard. They did point out some potential logic problems due to the nature of the reports I’m downloading from the software, so I will be working on adjusting the spreadsheet to incorporate the peculiarities of this software download. But so far, it’s looking good.