Excel Utilities and Backpack

Excel is outdated !

Ok, everyone knows Excel, you use Excel, I use Excel, Dad/Mom use Excel and maybe even Grandpa.

We all do reports, graphs, project planning…

In fact we use Excel for almost everything.

When Redmond’s company released Microsoft Excel in 1985, it was already a success and today Excel is extremely popular.

But you know what?

We never learned to use it.

Excel, this great unknown

Everyone has a different way of working with Excel. There is no clearly defined consensus or « good practice. »

The fact that we are not really trained in these kinds of tools leads to a lack of knowledge once we arrive in the professional world.

In fact, there is a classic debate on the web, like Python is better than Excel, Excel is outdated …

Python is used by guys who have learned to code for years or even decades, so this debate is largely biased.

And now I’m going to tell you the truth

Here’s what they blame Excel for:

Excel is not automatable

A programming language is integrated with Excel – Visual  Basic for  Application – ok, it’s more or less reserved for insiders, but honestly, it’s the simplest programming language i know.

Is he out of date?

Yes, but in fact there is not only VBA  in Excel, there is  M  (data manipulation),  DAX  (calculations as part of a relational model) and now  OfficeScript  (near Typescript/Javascript).

Oh, going back to VBA, there’s still a guy who finally made Zelda Excelda with:

Excelda

Excel is not scalable

Power Pivot limit: 2,147,483,647 tables, max lines numbers in each table: 1,999,999,997.

Graphic capabilities are limited

Certainly:

The Michelangelo of Microsoft Excel

A debate that costs money

This debate was born out of the rise of data science and the era of big data.

A whole range of new jobs have emerged: data scientist, data-analyst, data engineer, machine learning engineer, data-devops, etc.

The key word is DATA, and the main goal of these new trades is to create value from these large volumes of data.

Engineers, who learned Python at school, but not Excel.

And that’s how the gap was created:

On the one hand, there are people who use Excel in their daily work as they can.

And on the other hand the data analyst, data scientist, business intelligence trick who try to exploit this data, but end up with unstructured Excel files.

As a result, time and money are wasted.

And now the big secret: it’s possible to structure your data in Excel.

And we can learn that.

Why don’t we learn how to use Excel?

 


This page contains macros and videos that explain macros that allow to work efficiently with financial models. Many of the subjects in this page are in a file name “Generic Macros” which includes a whole bunch of macros and functions that may be helpful in just about any excel analysis that you are making. In particular there are two macros for colouring and for copying to the right — SHIFT,CNTL,R and CNTL, ALT,C (also SHIFT,CNTL,C). To use the Generic Macros macros, you just need to have the file open — the macros can be operated from any open file. Videos at the bottom of this page explain how to use macros such as copying a series to the right and colouring cells depending on the sheet colour. All of the macros and the VBA code is open and transparent meaning that you can try to understand the code, copy the code and modify the code. Ohter macros in the Generic Macros file contains VBA code that fixes various problems in excel such as finding and colouring external links, deleting blank rows, clearing sheets, finding the number or rows in a sheet and, making the lookup function into an interpolate function.

In addition to describing how to use, modify and make VBA code in the file named “Generic Macros”, I have included some general descriptions of how to use short-cut keys, how to effectively work with user forms and how create simple macros that help the goal seek a lot. These subjects are presented immediately below.

 

Generic Macros.xlsm

Video Review of Short-cuts

There are probably thousands of short cuts in excel (I don’t really know). Sometimes when I teach a class, I am told by participants that they will not be able to remember any of the short-cut keys I show them one day after the class is finished. My response is that you just need to know what you can do and the rest is like a cookbook. This is why I have created the files and videos below. The first point to understand is that some short-cuts are easy and really help and are worth a tiny investment in practicing. But like a lot of other things in life, you can take things to the extreme and over-use the short-cuts. So I have tried to discuss a few selected few of the short-cuts are really helpful.

Helpful short-cuts include ALT, E,I, S (Everybody Is Stupid, Egyptian Intelligence Service, Elvis Is Sleeping) that works really well with the SHIFT,CNTL,R macro explained in the Generic Macro section below. Other helpful short-cuts include ALT,E,M to move sheets around, ALT,E,L to delete a sheet, ALT E,S to paste special and SHIFT, ALT, right arrow and SHIFT, ALT, left arrow, The best short-cut may by the F11 key to make a graph and the ALT, F1 key to put the graph on the current page. To or delete a row or a column, the SHIFT, SPACE and the SHIFT CNTL keys combined with SHIFT,CNTL, + and SHIFT,CNTL,- can be helpful. All of these short-cuts are directly from excel and not associated with any of the files that I have made (i.e. Generic Macros”). Other useful macros create a new sheet, CNTL, N and format cells, SHIFT, CNTL, 1 to 7. One of the new short-cut keys is good for quickly freezing the panes and then un-freezing — ALT, W, F, F.

The video below walks you through these and other short-cut keys in the context of a credit analysis model and the files listed below are used in the videos. In the file named Project and Corporate Credit Example I have listed some of the short-cuts that I think are really helpful. The Project and Corporate Finance Example also is the file that is associated with the video below.

 

Project and Corprate Finance Credit Example.xlsx

Short Cuts and Bond Valuation.xlsx

Short Cuts.xlsx

Political Risk Exercise.xlsm

 

Video of Creating and Using Forms (Spinner Box, Combo Box, Check Box and List Box)

As with using short-cut keys, participants in classes that I teach sometimes complain that they will not remember how to use the forms — spinner boxes, drop-down boxes (called combo boxes), check boxes and list boxes. The video and file below are intended to be a reminder for how to used the developer tab and these forms. User forms or contols allow you to control inputs from different pages. This means that if you have an input in the assumptions page, say for the gearing percent of a project, this input can be controlled from multiple places including a summary sheet and a graph. These form controls can be very helpful in summarising data and making presentations with excel or just showing off to your boss. I use these forms extensively in other videos and files but I do not know if there is a place where I explain them slowly. The video below does this. It begins with explaining how to find the developer tab and then use the WINDSCREEN method to allow you to move the files (thanks to Tataynia in Russia). My son tells me the developer tab is like a secret handshake in excel — when somebody tells you they are really good at excel and there is no developer tab on their computer, you say yes you are really good. But you don’t believe them.

 

Sun Edison Case Study.xlsm

Video for Simple Macro with Goal Seek

As with the short-cut keys, participants in my classes sometimes say they will not remember how to make a macro. This little section is intended to be a reminder. You can do really fancy things with macros and functions that I hope are explained at various points in this website. This includes reading data from the internet, creating fancy scenarios, fixing excel to interpolate data, and resolving circular references. If you have not made a macro, however, do not be intimidated. The first macro I ever made sometime in the early 1990’s was to fix the goal seek in excel so you could repeat it without having to keep going back to the menu and keep changing the target etc. All you have to do is press the record button and then make your goal seek and then make absolutely sure to stop the record button once you have finished with the goal seek. This process is described in the video below. The file that I have used to explain this simple macro is associated with my WACC and interest shield stuff and listed below the video.

 

WACC and Growth with Goal Seek Macro.xlsm

Video for Creating your Own Short-Cuts

Some short-cut keys that would be really good are not included in excel. These may be short-cut keys to format your currency, to underline groups really fast or to put extra decimals in cells formatted as a percent. I have seen some people include all sorts of add-ins in their excel menu with scads of short-cut keys and other functions. I hope you agree that this is not very necessary. Instead, you can make you own customised short-cut keys. Then you will know what is going on and you will not cede control of your excel to anybody else.

The video below demonstrates how you can make your own macros. There is only one big rule here. Please place your cursor on the cell to be formatted and do not move it before you are finished recording the macro.

Introduction to Generic Macros

The generic macros file below is one of the most important on the website. It has a macro to copy to the right SHIFT, CNTL, R and it has macro name SHIFT,CNTL,C where you can colour cells depending on which sheet they came from. To run this macro, all you have to do is have the file open because you run macros from other open files (e.g. say you are in mybook.xlsm, you can open generic macros and run any of the macros in your sheet). I don’t like to take control of your computer and make add-ins, instead I think it is much better to just open the generic macros sheet and enable macros. Then the SHIFT, CNTL, C and the SHIFT, CNTL, R should work just fine. The generic macro file also has a lot of other macros that may be useful that are explained in the next sheet.

I have tried to make the macros flexible where you can search more rows and or columns to colour (which slows down excel). You can also adjust the SHIFT, CNTL, R to look up to different rows and test other things. To adjust the generic macro file, you go to the first sheet of the file and change the parameters. After you change the parameters however you must run the implement macros macro.

For every single one of the more than 1000 excel files on this website I have not put any passwords on the macros or functions. This means you can look at the code and try to improve it yourself. (If you have some good improvements I would really like it if you send me your work). You can also copy the macros from this file into your file or your personal workbook if you do not want to open generic macros every time you open your file.

You can go to google and find a list of short-cuts that probably has more than a thousand short-cuts (I have not bothered to go and check). I use just a few short cuts that I think are effective. Some are old — for example ALT E, I, S and ALT E, L and ALT, E, M which come from an old version of excel. These can be very helpful when used together with the SHIFT, CNLT, R macro. I have put the short-cut keys that I like in the file name Short-cuts.xlsx which is attached below. I have also made a video for the short-cut keys that I am in the process of revising.

 

Generic Macros.xlsm

Fill to Right.bas

Attach Multiple Checkboxes.xlsm

 

Miscellaneous Other Subroutines in Generic Macros

The generic macros file and a workbook named fm.xls includes a lot of different functions and macros. Some of the more important macros that create table of contents and remove the current sheet name from formulas and finds the links are shown below. As explained above, none of these macros have any irritating passwords and you can copy the macros into your files. The fm.xlsm file below has some of the older macros that I made a few years ago. The example calander shows how you can create a macro that puts a password in a file and uses MATCH and INDEX over and over again with conditional formatting to make a calander in excel (I doubt very much that you will use this).

Sub x_replace_sheet_name() Takes away current name of sheet in formulas
Sub x_Delete_cols() Deletes columns that are blank
Sub x_Delete_rows() Deletes rows that are blank
Sub x_fix_decimal() Fixes Problems with auto formatting
Sub x_find_rows() Finds number of rows in sheet
Sub x_find_cols() Finds number of cols in sheet
Sub x_colourTitles() Colours titles for the first column
Sub x_Create_Table_of_Contents_From_Sheet_Names() Creates Contents with links
Sub x_find_externl_links() Find External Links
Sub x_hide_sheets_after() Hide Sheets after Given Name

 

Table of Contents Example.xlsm

fm.xls

Example Calendar.xlsm

Functions in Generic Macros

In addition to macros, I put a bunch of user defined functions in the generic macros file. These macros do things like find the sheet name or the file name. They can also show who saved the file last and make a payback function. Unlinke the subroutine macros, you cannot just have the file open the functions. Instead, you must find the functions and then copy them into your file.

Function sheet_name(cell)
Function sum_labels(series)
Function File_name() As Variant
Function MyUDF(LastSaved1 As Boolean) As Double
Function Last_save_by() As Variant
Function LastSaved() As String
Function lookup_NA(lookup_value, test_array, result_array)
Function match_adj(lookup_value, lookup_array)
Function payback(series)
Function dpayback(d_rate, series)
Function period_of_year(period, timing)
Function show_formula(cell)
Function show_formulaR(cell)
Function show_formulaL(cell)

Functions for Working with Dates

The files below have functions that allow you to work with dates. Excel has a nice function that can be used a lot with the SUMIF function to aggregate data by quarter. But excel does not have a similar function for the end of a quarter. Functions that deal with dates are shown below. Another function that can be useful is one that evaluates how much time has occured between a month. This is very useful if you have a monthly model and are not assuming that each project starts or ends at the beginning or the end of a month.

 

Half year and Quarter.txt

End of Quarter.xlsm

end_of_qtr.bas

 

Timing Functions

The file below has functions that allow you to interpolate with a lookup type function. To use this, you need to copy the functions into your file. You can make a blank macro in your file. Then go to the Lookup and Interpolate macro and copy the entire contents of the Function into the macro you created. It works like the LOOKUP function in excel.

 

Lookup and Interpolate.xlsm

Look-up NA.xlsm

List Box with Multiple Entries

The file below has macros that allow you to use the LISTBOX Form with MULTIPLE selections. It is also described on the graph page, but I included it here because you much attach a macro to the list box to make it work with multiple selections. The list box can be effective in making graphs and many other things. The way you need to attach the Listbox to a macro is illustrated in the file below.

 

Flex Multiple Series on Graph.xlsm

 

The videos for various macros and the associated files are summarised in the following table:

 

Subject File Video Chapter Reference Page Reference
A video showing the short-cut keys that I use Short Cuts.xlsm https://www.youtube.com/watch?v=GprfRpmTPl0 Chapter 4 40
Short-cuts and Data Table Short Cuts and Bond Valuation https://www.youtube.com/watch?v=f_t_hbLaVik Chapter 4 40
Overview of Generic Macros – Operations Generic Macros.bas https://www.youtube.com/watch?v=Hz8GfMv7VP8 Chapter 5 45
Creating an Input Colour Macro from the F5 Function Colour Macro.bas https://www.youtube.com/watch?v=3Ino3YtZtcU Chapter 5 48
Working with macro that colours cells depending on the source tab colour Colour Sheets Macro.bas https://www.youtube.com/watch?v=UJ3pAKVbukg Chapter 5 48
Explanation of Shift CNTL R Macro that automatically copies to right from above Fill to Right.bas https://www.youtube.com/watch?v=YTZMPRt8J6I Chapter 5 45
Update and Improved SHIFT, CNTL, R Fill to Right.xlsm https://www.youtube.com/watch?v=ps1cXeJN-j8 Chapter 5 48
Explanation of how to use and make Table of Contents Macro Table of Contents Macro.bas https://www.youtube.com/watch?v=FrPE0tJWfOA Chapter 5 48
How to make a macro that displays the comments in the sheet with check box Comment box https://www.youtube.com/watch?v=6gz1gHHmuwU Chapter 5 48
How to create User Defined Functions Financial Library.xlsm https://www.youtube.com/watch?v=QY743V2BGrw Chapter 5 48
Demonstrates how to make a better LOOKUP function without NA Interpolate and Lookup https://www.youtube.com/watch?v=D9vJYvjf3i8 Chapter 5 48
Shows how to create look up function with interpolation Lookup-Interpolate.xlsm https://www.youtube.com/watch?v=sfokve3pRT0 Chapter 5 48
Demonstrates making a Look up function with NA Adjustment to Zero Lookup_NA.xlsm https://www.youtube.com/watch?v=oVBLRPmaE3M Chapter 5 48
How to use the generic macro file with exporting and changing macros Generic Macros.bas https://www.youtube.com/watch?v=JpztPJxDmOI Chapter 5 48
How to make an Add Text function that combines text Add Text.xlsm Chapter 5 48
Add Item to Menu in the Generic Macros file Generic Macros.bas https://www.youtube.com/watch?v=vEfTv3hVPDk Chapter 5 48
Create Function for Payback Period with intermediate decimal values Payback https://www.youtube.com/watch?v=7Cmbh_XMnks Chapter 5 48
Interpolate in Excel without Macro Interpolate macro https://www.youtube.com/watch?v=UAvwf-2nDlQ Chapter 5 48
Interpolate Macro with Selection.Cells Interpolate macro https://www.youtube.com/watch?v=3ArFFHZXI0g Chapter 5 48
Making a calander in excel Calander https://www.youtube.com/watch?v=FLtNFnnzs9Y Chapter 5 48
Installing Macros with File Import Fill to Right.bas https://www.youtube.com/watch?v=JvRndAlhp1w Chapter 5 48
Overview of Generic Macros – Code Generic Macros.bas https://www.youtube.com/watch?v=8-ezOD2YIkc Chapter 5 45
Installing Macros with File Import Fill to Right.bas https://www.youtube.com/watch?v=JvRndAlhp1w Chapter 5 45
Alternative ways to Import Generic Macros.bas https://www.youtube.com/watch?v=HBNxSzFXKR8 Chapter 5 45
Importing Macros to Excel Generic Macros.bas https://www.youtube.com/watch?v=44Cp4cvq-LQ Chapter 5 48
Function and Macro (fm) File fm.xlsm Chapter 5 48
Shows how to make macro to clear zeros from blank rows western customer list.xlsm https://www.youtube.com/watch?v=-di-zpxYlVQ
……………………………………………………………………………………………. ….. ……………………………………………………….. …. ……………………………………………………………………………………………… …. ……………………………………………… …. …………………………………………………….