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
Over the past few months, I have seen many videos showing how to create multiple folders at once using a .bat file (also known as a batch script). Often these videos leverage Excel to create the folder paths. Then, they undertake various manual actions to get the .bat file into the correct location, so they … Read more
Excel has a dirty little secret – The data validation does not stop a user from entering an incorrect value. That’s pretty shocking, as that’s the whole point of data validation. 😲 Many of us rely on data validation to ensure other users only provide valid data entry. However, many are not aware of how … Read more
You’ve built an amazing dynamic report; a user selects a business unit to view, and everything updates automatically – it’s beautiful. The only issue is that Company A has sales of $30,000,000, Company B has sales of $30,000. Displaying values in 1 decimal place millions makes sense for Company A. $30,000,000 becomes $30.0m. But it … Read more
No matter where you look, there is a consistent message that it is bad to merge cells in Excel; instead, we should use Center Across Selection. Whenever my friend Richard Sumner posts about his love of merged cells on LinkedIn, he is always met with many replies stating that merging cells is terrible. But here … Read more
Often as Excel users, we can look across at Power BI and see the convenience of having a sharable web-based dashboard. It’s nice to have each person viewing a dashboard without affecting other users. We don’t have anything similar in Excel… or do we? Normally, if we share an Excel Workbook saved in OneDrive or … Read more
Normally, we build user interfaces so that one button runs one macro. But what if we want the user to select which macro to run? How can we handle that scenario? Well, that’s what we are looking at in this post, how to run any macro from one button. Yes, you heard me. One button … Read more
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
You’re probably already aware that Excel has two main calculation modes: automatic and manual. I’m sure everybody would prefer to use automatic calculation; however, formula complexity and large data sets can cause spreadsheet calculation to be slow. As a result, some users set Excel to manual calculation mode. The differences between these two calculation modes can … Read more
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