This post looks at how to Resize an Excel sheet. It’s a common requirement for users and is especially useful when creating dashboards for different-size monitors. Every office I have ever worked in has been an equipment battleground. It didn’t matter what the equipment was; it just had to be better than everybody else. A new keyboard, Read More
Category: Tips & Tricks
Excel calculation mode keeps changing: How to fix it!
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
How to remove spaces in Excel (7 simple ways)
The space character for an Excel user can be a real problem. When exporting data from various IT systems, that export will often include extra spaces around values. Finding a way to remove spaces in Excel is a crucial skill for every user. We often can’t see the space characters, but Excel sees them. They Read More
How to display fractions in Excel (the easy way)
When using Excel, we often display numbers as decimals. However, that is not necessarily the best way for users to read the information; fractions can be a better option. We could enter fractions as text, but then we can’t use them in calculations. So, in this post, we’ll look at how to display fractions in Excel. About Read More
Excel Table absolute reference for cells, columns, and rows
One of the first things we learn in Excel is the magic of the $ symbol. It freezes the row or column, so the cell reference does not change when copying a formula. This is known as an absolute reference. With the introduction of Tables came a different (and more semantic) way to reference cells, called structured Read More
Excel formulas not calculating? 14 reasons & how to fix it
We have all experienced it, and often at the worst possible moment. For whatever reason, Excel’s formulas aren’t calculating correctly. The good news is that it is usually something simple. Once we know the most likely causes, it is easier to troubleshoot the problem. So, in this post, we look at the most likely reasons Read More
How to reference another workbook without opening: 5 DANGERS!
In this post, we look at using Excel to reference another workbook without opening it. While this may seem like a great idea, we need to be very careful. When using this technique, a lot can go wrong without us realizing it. Let’s explore this area. Please read to the end and ensure you know Read More
How to sum across multiple sheets in Excel – simple method
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: 4 simple ways
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
How to remove Excel passwords with VBA (all 5 types)
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 ideas on how Read More