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

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:

READ MORE

How to handle parts per million, basis points and per mille in Excel

Excel has built-in functionality to handle percent (%), but there is no automatic way to calculate parts per million, basis points, permyriad or per mille in Excel. It is easy to calculate though, but let’s start with the definitions:

And here’s how to apply it:

READ MORE

Create random text strings and line breaks with the CHAR function

How to generate a string of random letters in Excel

The CHAR function returns a character that corresponds to the number in the character set used by your computer (ANSI for Windows). There are up to 255 different characters in the set, with the capital letters from A-Z starting at number 65. Z is 90. To generate a random letter you can use the RANDBETWEEN function:

READ MORE

Join cells in Excel and remove excess commas between elements

EDIT: If you are using Excel 2019 or Office 365 you can use the TEXTJOIN function to solve this problem. Click here for the new article:

How to Join Text from Several Cells in Excel using TEXTJOIN

Unclean data can cause a lot of problems in Excel. In this post I will show how you can join data from different columns with a comma between them. That’s the easy part. The problem occurs when you have empty cells in your data, like in the table below. The result of the first row looks fine, but if you look at the rows with empty cells, you get too many commas:

EasyExcel_41_1_Join with concatenate and trim



READ MORE

Today’s Shortcut: Zoom in and Zoom out in Excel

This is a standard shortcut that works in most Windows applications, including all MS Office applications and all web browsers:

Hold down the Ctrl key and use the scroll wheel of your mouse to zoom in and out.

Of course, you can also use the zoom bar on the right bottom of the page, but when you know this shortcut you will probably never do that again!

EasyExcel_40_Zoom in and our in Excel

More shortcuts in Excel:



Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.