When would you use VBA macros in an Excel spreadsheet?

How do I ensure my formulas don’t break as my spreadsheet grows?

Microsoft’s favourite solution to expanding datasets is Excel tables. Any formulas referring to a column in a table will automatically include any data that gets added in the future.

Sometimes these aren’t practical for presentational reasons. An accountant might want to build a pro-forma containing incomes above expenses. Excel can be asked to subtotal the incomes using a simple formula like ‘=SUM(C3:C12)’. If I add an extra source of income in the middle, Excel will expand the formula to maintain the same top and bottom row. However, if I want to insert a new source of income below the existing sources, Excel will not automatically include it in the existing sum.

One option is to play a trick where you leave a blank row between the list of incomes and the total line. This would be row 13 and, as it’s blank, won’t affect the calculation. Your formula reads ‘=SUM(C3:C13)’ and your spreadsheet is futureproofed should somebody wish to insert a new row of income. This all assumes anyone inserting a source of income will retain the blank row and won’t just type straight into it. This can be achieved by making the blank row narrower.

Even so, it’s not unbreakable. A better alternative is to identify cells by their location relative to headers and totals. You can re-write formulas in that way, but it makes it harder for a user to understand the sheet. The best solution would be for the cell to read ‘=SUM(C3:C12)’ whilst being defined by its location in the pro-forma.

If you don’t want to compromise on a file’s reliability or its comprehensibility, you’ll need to incorporate a macro in the back end of the file.

How do I speed up my Excel file?

An Excel file can be slow to calculate due to heavy use of formulas, or slow to save due to the size of the file. Both tend to be a result of inefficient use of formulas.

Clients frequently provide us with files full of calculation sheets. Typically a first sheet will contain a large table of information, like a product list. A second sheet might contain a filtered and sorted version of the original table, with the second sheet being used to complete the output. The second table might be full of OFFSET, MATCH, RANK and VLOOKUP formulas designed to sort inventory by value and product line. Every time a user types ‘=VLOOKUP(A1,DataSheet!A:Z,5,FALSE)’ into a cell on the second sheet to pull across a ‘1’ or a ‘0’ from the first, Excel is storing an extra 34 numbers or letters, and having to re-calculate another formula. Repeat that 10,000 times and your spreadsheet grows and slows.

The simplest solution is to only pull data to the calculation sheet when strictly necessary and not view duplication as cost-free. If file size is the problem then using an Excel table so Excel doesn’t have to store every instance of a formula separately is also helpful. If the desired output is to complete a few cells then it may be possible to write array formulas that bypass a second sheet entirely.

Sometimes the best method is to summarise the raw data in a pivot table and perform further calculations on that. Excel processes and stores data far more efficiently in pivot tables.

Critically, a pivot table is only refreshed on request. You can also disable automatic re-calculation in Excel so formulas only re-calculate on request.

The problem with refreshing data on request is that users may not recognise they need to refresh or re-calculate the data after making changes. At that point, you will need VBA code installed in the file to ensure that when someone wants to view the file’s outputs, the data is always correct. Macros can be triggered by a user clicking on a report page and, in doing so, ensure formulas aren’t constantly slowing down use of the file.

How do I stop broken links occurring in my Excel spreadsheet?

If possible, don’t link formulas to other files at all. Often people build a new spreadsheet because the source file has underlying performance issues. Sometimes, a formula is linked to an Excel sheet that is being updated from an online database. In that case, the solution is to query the online database directly using an ODBC connection or PowerQuery.

Occasionally it is unavoidable to pull data from another Excel file. Then, if the source data moves or you e-mail your file, a user is helpfully informed that the file contains broken links. The problem is that Microsoft is very good at telling you which file is missing but not what it affects. Due to the way users copy and paste formulas between files, a lot of users will end up ignoring a broken links message.

Where the link to another spreadsheet is critical, the ideal process is for Excel to look for the file separately and, if it’s not where it was last time the file was open, prompt a user to locate it. The data is then pulled into your file without requiring a formula so there can never be a broken link.

This requires a macro. The Visual Basic code can also incorporate checks for data staleness so as to verify the source data is up to date.

How do I ensure my pivot tables and charts include newly entered data?

Excel pivot tables have a defined data range that may not incorporate any rows added to the bottom of the source data. If you link a pivot to an Excel table within a worksheet, any new rows should be incorporated. You will also need to manually refresh a pivot table each time you add more data, whilst ensuring the pivot table has the physical space to display any output rows or columns required.

Another problem may be that the pivot table itself is filtered. If you filter a pivot table to show red and orange pencils because you don’t want to see your yellow stock, then any green pencils added later won’t be included. So it’s always worth removing filters from a pivot table if you’re unsure as to where your data has gone.

Aside from using an Excel table, which isn’t always possible, the simple solutions offered above are all manual processes. If you want a pivot table that automatically shows any new pencils that aren’t yellow, you need a VBA script updating the filter whenever the table is refreshed. The code can also ensure your pivot tables and charts are refreshed each and every time you look at them.

Where clients expect their dataset to grow and have not been able to use an Excel table, we typically see them defining pivot tables to cover the first 1,000 rows of a spreadsheet, giving their file ‘room to grow’. This creates a data integrity problem when the source data inevitably expands to row 1,001. A further problem is that the pivot table will see the blank data as a category in its own right that will then need filtering out for presentational purposes. This serves to recreate the ‘yellow pencil’ problem whereby you can’t permanently remove the blanks whilst expecting new products to show up in the table.

The solution is then to use a background macro to define the pivot’s data range whilst handling any other issues that arise.

How do I restrict data entry to a dropdown list in Excel?

Microsoft Excel comes with a data validation function that lets a user list the items a user should be allowed to select. This can be done as a comma separated list within a cell (such as ‘Yes, No’) or, better, with a link to cells containing the permitted data. The link can be to a named range which can be a single column Excel table. The benefit of a table is that the options can be revised as required and easily sorted alphabetically.

In sales or inventory management, users often want to go further and create a dependent dropdown list. That means if I select ‘fruit’ from my first dropdown, I can then select ‘apple’. Whereas if I select ‘vegetable’, I can select ‘carrot’. Excel lets you enter a formula as the source for a dropdown list. A popular solution involves using the MATCH function to identify a second list containing data in the category selected from a first list. Once set up, it works but can be awkward to update. It also becomes unwieldy if you want to add a further sub-category.

Our clients find it easiest to manage a single table of products in which they can enter fruit->orange->jaffa. Having selected orange from a dropdown list, they would expect their third dropdown to be restricted to varieties of orange. This sort of solution can be built using Visual Basic that responds to each selection in turn. It is most easily built into a pop-up form though the dropdowns can be configured to update as a user clicks around a spreadsheet.

There are two further advantages to Visual Basic forms. Firstly, if a user has to select from a dropdown list in a cell, they may copy the data from another spreadsheet. That source cell will not be linked to the same dropdown list so a user can bypass the restrictions you’ve wanted to impose on the user. Secondly, a form can consolidate a user’s selection into a single output cell. If you’re building a quote, it can be nicer to display ’Jaffa Oranges’ than ‘Fruit/Orange/Jaffa’ in three separate columns. Excel forms and macros can neatly separate the process of entering data from the output.

How do I protect my historic data in Excel?

The best way to protect historic data is to back up your Excel files which can happen automatically in Sharepoint or OneDrive. If you’re using historic data in charts such as year-on-year comparisons, it isn’t enough that you can retrieve any lost data as it needs to be present and always correct. The best solution is then to hide rows of old data, or migrate it to archive sheets.

Sometimes it is not practical to hide old data because you wish to retain it as a reference. At Excel4Business, we see this most frequently with order lists. Clients want to keep their sales history together so they can build pivot charts to show trends over a 5 year period. They may want a table they can filter to complete occasional tasks like building remarketing lists. Splitting or hiding the data doesn’t really work.

The next option is to protect rows containing old data. Excel lets you lock cells and protect a worksheet. This can stop a user from sorting the data and it relies on someone occasionally going into the file to lock any data that is considered complete. In real world use, those are often unacceptable compromises.

We find macros offer a better solution. Macros can selectively prevent users from editing cells. This could be based on a row’s order status, or be based on some sort of date. One advantage of using Visual Basic is that editing historic data can be restricted instead of simply being forbidden. If a user wants to edit a historic order, they can prompted for a password. Editing rights can even be granted based on a user’s identity.

How can I find what I need in my large Excel tables?

Excel gives users the option to sort data alphabetically or by numerical value. The filter function provides a number of options, allowing you to find cells containing snippets of text or dates within defined ranges. If you need to search through less structured data, the Find function can be accessed using ‘Control + F’, with the added advantage that it can look within formulas.

Our clients often want to browse lists of addresses. It’s very easy to find every instance of ‘New York’ in a single ‘city’ column. If instead you have three separate address columns for address lines 1/2/3, it can be harder. If you filter for ‘New York’ to appear in the third line of an address, you may be missing addresses where it appears in the second row.

The inefficient solution is to filter the table twice every time you want to find ‘New York’ and copy the output elsewhere. The long-term solution might be to restructure the data to use a separate city column.

An alternative approach is to write a formula that looks up a search term across multiple columns. You can combine the IF and FIND formulas to identify when any of a group of cells contains a term such as ‘Texas’. It can return a ‘1’ when the term is found and a ‘0’ when it is not. In practice, the search term can be entered into a box above the table. You then just need to filter the output for a ‘1’ every time you enter a search term.

The problem is that filtering a formula column every time you enter a search term is very cumbersome. Removing the filter once the search is complete is even more annoying. You can write a Visual Basic macro that triggers whenever a search term is entered. Additional macros can provide quick access to default filters through buttons on the worksheet.