Excel Top Tip #9 - Level up in four new areas

Here is the email sent to all subscribers on 4 March 2021. To receive Excel top tips just like this, you can subscribe by clicking here.

Hello

Finding areas you never knew existed in Excel will help you to make leaps forward at work, saving you time doing repetitive tasks, gaining a foothold as the ‘go to person’ in your team, and building better relationships with clients and managers or partners based on the continuous improvement of the service you provide.

More power in Excel can come from just a few minutes exploring any of these little-known areas. And the best thing is you don’t need to install anything, just absorb and store the tips for the next time you…

1 - Find and replace text

When the word or acronym you are looking for changes in the bank statement narrative, a line of investigation can be unnecessarily time consuming to pursue.

Wildcards written within the search term of “find & replace” allow certain other characters to match, without needing to define them exactly.

For example HM*C will match “HM Revenue & Customs” and “HMRC” but not “HMV”.

2 - Visualise a list of bank balances

We’re hardwired to absorb graphics much quicker than numbers. Fortunately representing your list of balances in a graphic form is as easy as selecting the colour you like from some conditional formatting options - get started by clicking Conditional Formatting -> Data bars.

Click More Rules and tick “Show bar only” to remove the numbers from the cells, like here:

It’s another little-known trick that will make you look good:)

3 - Highlight transactions based on amount

Exploring data and presenting your findings with context is key, so highlighting transactions based on your criteria is a good skill to have stored up for your next investigation.

Say you’re looking for payments over £1,000, simply select the transactions and enter this formula into the conditional formatting for the cells containing transactions:

4 - Categorise transactions

Limit the input for cells in a column with a dropdown box, for faster data entry.

And it’s more accurate - you won’t misspell entries making subsequent analysis of the categories difficult.

Click Data -> Data validation -> Data validation to get started, then set it up, like this:

It’s a simple yet seldom used feature of Excel that can save you time and help you keep your worksheets tidy when working with others.

Keep these tips someplace safe or bookmark our knowledge base for the next time you’re wondering, “is there a quick way…”

And, if you have any questions, please get in touch; why not book a call to talk about how we can help you?

David - The StatementReader Team