The easiest way to reduce file size in Excel: Excel Binary Workbook

Posted on February 6, 2021 by Audun Danielsen

Sometimes an Excel document becomes too large to email or share via your filesharing service. Or it just takes forever to calculate. How can you speed up your spreadsheet or reduce the size of it?

Obviously, you should always look at the structure of the file and see if you can minimize the use of volatile functions such as OFFSET, INDIRECT, RAND, TODAY etc. The volatile functions recalculate every time you make a change in the workbook. It is also a good idea to be careful with Conditional Formatting, which is also a volatile feature.

But the easiest way to reduce the size and increase the speed of you file is to save it as an Excel Binary Workbook:

An easier way to calculate compound interest in Excel

Posted on January 27, 2021 by Audun Danielsen

Do you want to calculate how much your current savings might be worth in 10 years or in 30 years? It’s an easy calculation that doesn’t require any specific function in Excel. Some simple multiplication, addition and exponentiation is all you need.

Still, the answer you will most likely get if you search for “compound interest in Excel” on Google is the FV function. The FV function is difficult to use, and it actually calculates compound interest based on a monthly rate rather than a yearly rate, which gives a slightly different result.

How to find cells that contain a formula in Excel

Posted on January 2, 2021 by Audun Danielsen

It’s a good idea to check if there are actually formulas in all the cells where you expect them to be. It happens all the time that someone accidentally has overwritten a formula with a hard-coded value, and it can be difficult to spot errors like that. Fortunately, there is a very easy way to locate all the formulas in an Excel report: The Go To Special feature.

This is what a sales report might look like. We expect to find formulas in some of the columns, but it’s almost impossible to go through the report manually cell by cell:

Unhide all sheets in Excel with a simple macro that works for all your documents

Posted on December 25, 2020 by Audun Danielsen

Sometimes you have to deal with an Excel file that has a lot of hidden sheets, and Excel only lets you unhide them one by one, so it can be quite annoying. Here’s an easy trick that lets you forget that you ever had this problem. It requires VBA, but it’s very simple, and you can save it as a personal macro that works for every Excel file you open on the same computer, so you never have to do it again. I’ll explain every step below.

First, make sure you have the Developer tab in the ribbon. If you don’t have it, right-click on any of the other tabs, choose Customize Ribbon and click the Developer check-box:

How to get Week Numbers right in Excel using ISOWEEKNUM

Posted on December 20, 2020 by Audun Danielsen

Excel has had the WEEKNUM function for as long as I can remember, but it is very confusing, and you tend to get it wrong more often than you get it right. This is what it looks like when you type the WEEKNUM function:

Who’s to know that it is actually the last option (21) that’s the right one for most users?

Thankfully, a few years ago, Microsoft finally launched the new ISOWEEKNUM function, which I’ll describe in a moment. But first, let’s look at the different ways to calculate week numbers.

How to Join Text from Several Cells in Excel using TEXTJOIN

Posted on November 29, 2020 by Audun Danielsen

I wrote an article a few years ago about how you can join data from different columns, and add a comma between each part. It was quite tricky, especially if we had some empty cells, so we ended up with a long formula with SUBSTITUTE, TRIM and CONCATENATE.

If you have Excel 2019 or Office 365, there is an easier way: The TEXTJOIN function.

Here’s the same dataset that I used in the previous article, and the result we want in the column to the right:

Easy-Excel.com

Find Easy Solutions to Your Excel Problems!