Master These 5 Excel Hacks and Save Valuable Time

Microsoft Excel is a powerful tool, and it’s not just for numbers. More and more people are using Excel to manage tasks in their everyday lives.

One thing every Excel user has in common is not knowing all of the intricate tips and tricks to get the most out of Excel. There are so many functions and formulas to help you slice and dice numbers or give that data a new look, it’s impossible to recount them all.

We’ve put together a list of five of our favourite Excel hacks that are easy to master, to save you time and impress your colleagues.

1. Flash Fill Your Data Just Like Magic

Want to manipulate your data as quick-as-a-flash? Don’t rely on a complex combination of functions such as FIND, LEFT, CONCATENATE, or Text to Columns. Flash Fill is a powerful and time-saving tool that automatically fills your data when it senses a pattern.

Let’s say you have a list of GL accounting codes, and you want to extract the integer part of the code. Just complete the first cell, and Excel will generate a preview as you type to complete the list based on your provided pattern. Press Enter to accept the suggestion.

Just like magic, your data entry is complete and free from errors.

You can even use Flash Fill to combine and adjust your data.

To turn on Flash Fill go to File > Options > Advanced > Editing Options > select the Automatically Flash Fill box.

Or run it manually by clicking Data > Flash Fill, or use shortcut Ctrl+E.

Things to remember:

1. Flash fill works best when the source data is consistent.
2. Flash Fill is not dynamic, so if you change the source cell, the result doesn’t update automatically.
3. To ensure that Flash Fill recognises a pattern, you need to use Flash Fill close to the source data.

2. F2 is Not the Only Way to See Where That Number Came From

When you’re trying to work out exactly what is behind your profit and loss (P&L) figures (or any figure), you can read the formulas, and when you’re editing a cell, Excel has the good sense to highlight the precedent cells (the cells referred to in the selected cell).

Nice right?

Except when the spreadsheet is so complex that all the precedent cells are not visible in the same window. They could be anywhere. And then you have to go and find them. And you lose the other cells. And the source formula.

OR

Use Formula > Trace Precedents. Excel will draw some nice (persistent) arrows for you, from the selected cell to its precedents.

Or its dependents (the cells which depend on the cell you’re looking at) even!

And you can even select Trace Precedents or Trace Dependents again, to have arrows pointing at the precedents of the precedents for your cell

When you have covered your spreadsheet with little blue arrows, you can Remove Arrows to clear the air.

If you haven’t already, give it a try. We’re sure you’ll love it.

You can even take it up a notch with some shortcuts:

• Press Ctrl+[ to go straight to the precedent cells
• Press Ctrl+] to go straight to the dependent cells
• Double-click on the dotted line to an off-sheet reference to go straight to it

 

3. Quickly See Insights Into Your Data With Conditional Formatting

Want quick insights into your data? Conditional Formatting can help you make sense of your data by highlighting trends or patterns in your data based on rules that you create.

You can apply conditional formatting to a range of cells, an Excel table, or even a PivotTable report! You can even apply multiple conditional formats to the same cells.

Option A. Use the Quick Analysis button or CTRL+Q to display the pop-up menu.

1. To see a live preview of your data with formatting applied, hover over the different options in the Formatting The formatting options differ depending on the type of data you have selected, for example, if your data contains only text as opposed to text and numbers.
2. Click on the formatting option you want.

Note: If you change the data in a cell, so that it satisfies the rules, the format automatically changes too.

Option B. If you want more control over what type and when conditional formatting applies, you can create your own rule. Use this option to format blank cells or error cells.

1. Go to Home > Conditional Formatting > New Rule.
2. In the New Formatting Rule dialog box, select a Rule Type and then Edit the Rule Description to set your specific parameters.

In the following example, we have used a formula to highlight rows where our Actuals for Costs exceeded our Budget, by looking at whether the Variation column (column G) is negative. The rows where the formula G<0 is true, are highlighted.

The result of conditional formatting is below:

 

To present your data more clearly, you can also sort or filter your data based on conditional formatting.

4. Simplify Your Workbook By Hiding Data in Plain Sight

Hiding a row or column in Excel is simple – just select the whole column by clicking the letter (or number for row) header, right-click, and select “Hide”. You can unhide by selecting the columns to either side of the hidden column, right-clicking, and selecting “Unhide”.

But what if you have a section of inconveniently placed data you want to hide, but you still want to be able to work with?

1. Highlight the cells you want to hide > right-click > select Format Cells….
2. In the Number tab, select the Category type “Custom”.
3. In the Type field, input three semicolons ” ;;; ” and click OK.

 

Now the numbers aren’t visible, but you can still use them in formulas. The values of these cells can be found in the preview area next to the Function button. You can take this hack even further.

When collaborating with a team or integrating data from different sources, your Excel workbook can get overloaded with several sheets (each indicated by a tab at the bottom). To simplify your workbook, you can hide sheets, making their data still available not only for reference, but also available to formulas on other sheets in the workbook.

1. Right-click the bottom sheet tab and select
2. To find it again, go the View tab at top, click Unhide, and pick the sheet name from the list of hidden sheets that pops up. Note that you can also hide the sheet you are currently working on by clicking Hide in the View

 

5. Keep Your Data Clean With the Data Validation Function

We understand the importance of clean data (see Tactics to keep your data clean), especially when you’re collaborating with teams across your organisation.

When you’re creating a spreadsheet for others to use, consider restricting what data users can input to ensure data entered is valid. Use Data Validation to restrict the type of data or the values that users enter into a cell. You can even create the error message they’ll see.

One of the most common data validation uses is to create a drop-down list:

1. Highlight the cell(s) you want to create a rule for.
2. Go to the Data tab > Data Validation OR use Alt > D > L keyed separately to open the Data Validation dialog box.
3. On the Settingstab, under Allow, select List for drop-down menu. Note that there are other options that allow you to restrict to whole number, decimal, date, time, or text length.
4. Click in the Sourcebox, then either type a list, with commas between the options OR click the button next to the Source field and then select your list range. You can also insert custom formulas, for example ISTEXT or ISNUMBER to restrict the type of data.

Tip:  Having your list items in an Excel table means that as you add or remove items from the list, any drop-downs you based on that table will automatically update.

5. Check the In-cell dropdown box.
6. Click the Input Message If you want a message to pop up when the cell is clicked, check the Show input message when cell is selectedbox, and insert a title and message.
7. Click the Error Alert If you want a message to pop up when someone enters something that’s not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and insert a title and message.
8. Click OK.

Another common user error is inputting a future date into a form or for a transaction. In this example, we prevent future dates from being entered into Excel by using the Data Validation function and TODAY formula.

1. 1. Highlight the cell(s) you want to create a rule for.
   2. Go to the Data tab > Data Validation OR use Alt > D > L keyed separately to open the Data Validation dialog box.
   3. In the Settings tab, select Date as the Allow
   4. The Data field can be either less than or less than or equal to. This is essentially whether you want to include the present day in validation.
   5. Enter an End Date of =TODAY() – this formula retrieves the present date.
   6. (Optional) Click the Error Alert tab and modify the error message.
   7. Click OK button to apply the validation.

Note: Although you can enter any date into the End Date or any date related field in the Data Validation dialog, using the TODAY formula makes the validation dynamic

For other tips on keeping your data clean, read our blog about Tactics to keep your data clean.

To learn more about bringing your data in line, contact us, today.