Tables are one of the best features of Excel. While it is possible to use the standard cell referencing with a Table, they have their own referencing style called structured references. We have to think a little differently to create a running total in an Excel Table using structured references. We will look at all Read More
Category: Tips & Tricks
Why are Excel formulas not calculating?
We have all experienced it; for whatever reason, the formulas in Excel aren’t calculating as expected. Most of the time, it is something simple that we have never thought of. But, once you know the most likely reasons, it is easier to troubleshoot the problem. So, in this post, we are looking at the most Read More
Why does Excel’s calculation mode keep changing?
You’re probably already aware that Excel has two main calculation modes: automatic and manual. Automatic calculation – calculates when a value changes or based on specific actions (inserting, deleting, hiding rows/columns, or renaming a worksheet, etc.). Manual calculation – only calculates when explicitly requested by the user: From the ribbon, click Formulas > Calculate Now Read More
Sum across multiple sheets in Excel
Have you ever had to sum the same cell across multiple sheets? This often occurs where information is held in numerous sheets in a consistent format. For example, it could be a monthly report with a tab for each month (see screenshot below as an example). Watch the video Watch the video on YouTube. I Read More
How to split cells in Excel
There are many circumstances where we receive information with multiple data points inside a single cell. This often occurs when the data’s original intention is slightly different from how we intend to use it. In these circumstances, we often need to split a cell into its constituent parts. This post will look at solving this Read More
Resetting the scroll bar in Excel
Have you ever experienced the issue where Excel’s scroll bars become tiny? A small movement in the mouse can change the view by hundreds of rows or columns. This makes the worksheet exceptionally difficult to navigate around. The problem might reveal itself with an error message when trying to insert new rows or columns. In Read More
Crack Excel passwords with VBA
There is nothing more frustrating than finding out a previous employee has used passwords to protect an Excel file, and it turns out nobody else knows the password. Or maybe it’s worse when it’s your file, your password and you’ve forgotten it. Either way… you’re stuffed! However, before you give in, let me share some Read More
Remove blank rows in Excel
Data comes in all shapes, sizes and forms, so it’s not uncommon to find a dataset with a lot of blank rows. Sometimes it’s not an issue; other times, it causes big problems. Therefore, the best practice in Excel, is to remove blank rows. If there are only a few blanks, we could do this Read More
Spreadsheet Day 2018
Happy Spreadsheet Day! 17th of October is Spreadsheet Day, the day each year when we celebrate… you guessed it… spreadsheets. VisiCalc, the first spreadsheet application, shipped on 17 October 1979. Originally it was only compatible with the Apple II and cost $99. I don’t know if VisiCalc had formulas and functions. Sometimes I wonder what Read More
Excel Tables – Absolute cell & column references
One of the first things we learn in Excel is the magic of the $ symbol. It freezes the row or column, so when copying a formula, the cell reference does not change. With the introduction of Tables came a different (and more semantic) way to reference cells, called structured references. However, structured references don’t Read More
