Here is the email sent to all subscribers on 23 April 2020. To receive Excel top tips just like this, you can subscribe by clicking here.
From now on, each month StatementReader will be landing in your inbox with a Top Tip on how to get the absolute most from Excel. Consider it one way of us helping you excel in Excel (sorry!).
But first things first. In these uncertain times, and as the spread of Coronavirus forces businesses to take action and implement work from home schemes, we want you to be certain of our unwavering support as you aim for business continuity from your kitchen table.
If you’re used to asking a member of your team for help when using Excel, instead, you can send us an email or give us a call – no request for help is too big or too small. And if you’re in need of some advice on how to get the most from StatementReader as you navigate the Coronavirus conundrum from home, we can point you in the direction of our online hub of advice and application updates. Consider StatementReader your secret weapon in getting stuff done.
Now, on to FInEx.
The first step towards working effectively with new data in Excel is absorbing or internalising the data. Depending on your method of learning, you might be less suited to using tables of numbers – which aren’t typically easy to digest – and may instead prefer to use visual or graphic representations of data to extract meaning from.
As such, using one click-features to add colours and graphs to your table of bank transactions can advance your ability to:
Top Tip #1 - Sense check the values
Conditional formatting is a way to quickly find patterns and extremes in your data. Popular built-in conditional formatting options are:
See which cells are blank
Check if dates are overdue, and
Compare two cells
This is where you can find these options:
☝️Your handy keyboard shortcut: Alt > O > D
Bespoke options are available. Simply select ‘use a formula to determine which cells to format’ option to surpass the limitations of the built-in, one-click options and specify exactly what you want shading.
Here are some of the most popular requests for colouring cells that can be solved using this more flexible feature:
Based on another cell
Using multiple conditions
Based on date
Show which cells contain text, and
If cell values are greater than or less than x
The key is to create a formula that evaluates to either ‘true’ (in which case, the cell would be shaded) or ‘false’ (when the cell wouldn’t be shaded). Here’s how:
👇Your handy keyboard shortcut: Alt > N > SL
Another technique that the Microsoft Office Specialist course teaches us is to use sparklines. This can help to visualise and extract patterns or anomalies from a list or matrix of numbers.
Here is the typical outcome:
We hope you’re able to implement this advice and soon begin to gain a deeper understanding of the data you’re presented with. Doing so will enable you to gain more confidence in your role and use of Excel, meaning you make a real difference to your organisation and clients.
The StatementReader Team