excel top tips save time

Excel Tips – Ultimate Tips and Tricks to Save Time

In > All Sage Blog Posts, Process Improvement by Harry Phillips

Spread the love

There’s generally a tool for almost anything finance-related that you would like to do in Excel. The issue is that sometimes it’s quicker to work your way around a problem rather than find the specific tool you need for one off issues.

There are also some things that are well worth learning, as you will use them time and again, and again, and again! At Acuity Training we run a number of Excel courses and the a-ha moments are always the same.

Let’s take a look at three Excel tips that will save you time on a regular basis.

1. Learn A Few More Keyboard ShortcutsExcel Tips - Ultimate Tips and Tricks to Save Time - Sage UK

Most people only know CTRL + C / CTRL + X / CTRL + V.

There’s no point in learning more than about 10 of the 250+ shortcuts in Excel, but broadening your shortcut repertoire a little can speed things up a lot.

Absolute Formulas
KEYSTROKE: F4

Stop wasting time adding dollar signs manually to absolute formulas… simply press F4 when your cursor is in any part of the reference and you will lock the reference for that cell.

Press F4 a second time and you will only lock the row number, not the column number.

Press F4 a third time and you will now only lock the column number and not the row number.

Press F4 a fourth time and the formula returns to where you started and is unlocked.

Jump To The End Of Your Data
KEYSTROKE: CTRL + UP / CTRL + DOWN / CTRL + LEFT / CTRL + RIGHT

If you are working in a very large table it’s very useful to quickly jump to the end of the data and avoid watching endless frustrating scrolling.

Simply hold down CTRL and then pressing the arrow button for the direction you want to go. You will jump to the end of the data in whichever direction you choose.

Jump To The Next Tab In Your Spreadsheet
KEYSTROKE: CTRL + TAB

This will take you to the next tab in the your spreadsheet.

Select A Whole Column Or Row
KEYSTROKE: CTRL + SPACEBAR / SHIFT + SPACEBAR

To select the whole of the column that is currently active simply type CTRL + SPACEBAR.

To select the whole row rather than column use SHIFT + SPACEBAR.

If you would like to learn more shortcuts, and other Excel tips, this website has great resources.

Get started with Sage 200 Excel Reporting! Check out the blog here…

2. SumIF and CountIF

These formulas are little known extensions of the SUM and COUNT formulas that everyone is familiar with. They save huge amounts of time when working with large datasets.

CountIF is especially useful as Count doesn’t work in filtered data.

Both of these formulas work in the same way. They only sum or count the relevant data if it meets the criteria that you set.

Let’s look at a very simple example, imagine you want to count or find the total value for the invoices from a Supplier AA from a table of data.

Excel Tips - Ultimate Tips and Tricks to Save Time - Sage UK

As you can see you simply insert the range that you want to search as the first argument of your formula. The criteria that you want to search for Is the second argument of the formula.

For SUMIF there is then a final argument that is the range from which you want to draw the data to sum, assuming that it isn’t the same as the range of data you want to search.

It is possible to use multiple criteria on multiple data ranges with these formulas. They can be hugely powerful. More complex applications of SUMIF as covered in this article.

3. Find And Replace

Find and replace is a far more powerful use of the find function that most people know.

By using Excel tips #3, you will be able to amend each piece of text that meets the criteria that you set.

For example, if a supplier is bought by another business that you already supply, or changes its name, you might want to change it’s existing name in a dataset for a new name.

Find and replace can do this in seconds. For simplicity lets change supplier AA’s name to BB in our previous dataset.

First you need to select all of the data that you want to work on.

Then press CTRL + F to bring up the Find dialogue box as below.

Click the highlighted ‘Replace’ tab to go to Find and Replace.

Excel Tips - Ultimate Tips and Tricks to Save Time - Sage UK

This will bring up the Find and Replace dialogue box where you simply work through the steps in order.

Excel Tips - Ultimate Tips and Tricks to Save Time - Sage UK

1. You need to enter the text that you want to find
2. Enter the text that you want that text to be replaced with.
3. Click on ‘Replace All’ and Excel will then search through all of the highlighted cells and replace the contents of all of the cells containing AA with BB.

If you’ve not got this quite right remember CTRL + Z will undo the changes you’ve just made!

Now… use these Excel tips and enjoy all of the time you save!


Author Bio:

Ben Richardson is a director of Acuity Training one of the UK’s learning providers of classroom based Excel training.

 

Excel Tips - Ultimate Tips and Tricks to Save Time - Sage UK

Other Good Reads


Spread the love
Harry Phillips