10 Unknown Excel Hacks

Excel will never leave the project management arena so it's important to make it as quick and easy as possible.

Take a look at our top tricks for using Excel in 2021:

1 – Sorting Via Custom Lists

Sometimes you need to sort text not alphabetically. Maybe it’s for days of the week or months. This feature takes a bit more time to set up, but once you do it for a set of data, it’s really easy to use that data in the future.

• To set this up, first go to File > Options > Advanced.
• Under General, click the button Edit Custom Lists.
• Enter your list, then click OK twice to get back to your data.
• Next, select the data you want to sort, and go to Data > Sort.
• Under Order, select Custom List.
• Select the custom list you want to use, and click OK twice to get back to your (now custom sorted) data.

2 – Number As Text

Numbers are formatted as text and do not work well in formulas.

Here is a quick fix:

• Add 0 to the cell. This will keep the number intact and can be used in formulas.
• A lot of people use the apostrophe (‘) before the number to make numbers as text. This excel trick would work in this case as well.

Quick upgrade to your Excel skills, Source: The Express

3 – Fixing VLOOKUP/MATCH with TRIM

You can see that the 2 values match perfectly, but the VLOOKUP or MATCH formula would still say there is no match.

Extra spaces are often the culprits here. If the text has leading or trailing spaces (or more than one space between words), excel would not consider it as an exact match, and your formula would throw up unexpected results.

The way around – use the TRIM function. This function removes any leading and trailing spaces, and any extra space between words.

4 – Copy Visible Cells Only

You need to just copy and paste what you can see? But instead the all of the rows and columns get included!!!

Try this:

• Select the cells and press Alt +; (this selects visible cells only).
• Now paste anywhere and only visible cells gets pasted.

5 – Quickly Debug Formula

Missed a comma, wrong reference, missing argument, wrong parenthesis position? The list of possible causes of formula errors could be endless.

Debugging a formula could be painful, especially if it has been created by someone else. Here is a way to quickly debug a formula:

• Select the cell that has the formula.
• Go to Formulas –> Formula Auditing –> Evaluate Formula (Keyboards Shortcut Alt + TUF).
• Click on Evaluate to see the steps the formula is evaluated by Excel.

6 – Quickly Delete All Comments

Teams often re-use templates that have comments. To remove all the comments is one of the worksheet operations can save users a lot of time. Here is a quick way to select all the comments at one go and delete all:

• Select all the cells.
• Go to Home –> Editing –> Find and Select –> Go to Special.
• Select Comments in the Go To Special Dialogue box.
• Click OK. This will select all the cells that have comments in it.
• Now go to any of the selected cells, right click and select Delete Comment.

7 – Switch Between Excel Spreadsheets

Do you normally have 100s of tabs open at any one time?  Google Chrome, Excel, PowerPoint, Kindle, Mozilla Firefox, Email, and many more applications open at the same time.

Most use Alt + Tab to cycle through different open applications, but what if you want to cycle through open Excel Workbooks only.

Use Control + Tab instead.

8 – Quickly Scroll to the Right in a Huge Data Set

If you have a huge data set that covers a lot of columns (200 columns+). And  have to scroll through your data often. What are the ways you can do this?

• Usual way 1: Use the right arrow key (and cover each column one by one).
• Usual Way 2: Leave the keyboard, get hold of the mouse, select the scroll bar and scroll.

Here is the third not-so-usual way – Use Alt + PageDown Key. It does what Page down does for rows. It jumps 20 columns at a time.

9 - The XLOOKUP Function

By the end of 2021, no one will think twice about a vlookup function unless they open up an ancient Excel file.

That’s because in 2019 Microsoft unleashed the XLOOKUP function.

XLOOKUP contains the functionality of VLOOKUP, HLOOKUP, IFERROR, and INDEX/MATCH, all in one streamlined Excel function.

10 – Combine Worksheets

How much time do you waste copying data from one sheet to another?

Stop wasting that time and instead combine all of those worksheets using the “Consolidate” option and here are the steps for this.

• First, add a new worksheet and then go to Data Tab ➜ Data Tools ➜ Consolidate.
• Now in the Consolidate window, click on the upper arrow to add the range from the first worksheet and then click on the Add button.
• Next, you need to add references from all the worksheets using the above step.
• In the end, click OK.