INTHEBLACK May 2022 - Magazine - Page 69
CLICK HERE TO ACCESS
Extra material, a podcast
and a video from
Neale Blackwood CPA
Power Query allows you to
extract raw data from virtually any data
source (even PDF files)
fix issues with the data like bad dates;
missing codes or extra spaces
create a data table that can be used by
a Pivot Table
combine data from multiple files into
a single data table
convert report layouts into data tables
•
•
•
•
•
The process is created once and then the
steps are re-run whenever you refresh the
data. Little or no coding is required for most
tasks. There is a programming language
(called M) that allows you to handle more
complex data cleansing processes.
POWER PIVOT
My previous article introduced this topic.
The Data Model was added in Excel 2016.
It allows you to treat tables in Excel like a
relational database and create relationships
between tables. This adds another dimension
to Excel’s reporting possibilities.
Prior to the Data Model, Excel only handled
two-dimensional tables. A pivot table could
only report on a single table. The addition
of the Data Model is like adding a third
dimension to your tables. Pivot tables can now
extract data from multiple tables in one report.
DYNAMIC ARRAYS
I did a series of three articles from May to July
in 2020 that featured this new calculation
technique.
This technique will change the way
spreadsheets are developed in the future.
Formulas automatically spill down and across
as far as they need to, from a single formula.
You can work with a range of values in the
same way you used to work with a single cell.
It offers so many opportunities to simplify
reports and dashboards while making them
more flexible.
DATA VISUALISATION
This isn’t exactly an Excel skill, but is part of
producing effective charts (graphs) in Excel.
With the rise of Power BI for dashboards,
more focus has been aimed at producing
better charts that convey a message with
a minimum of elements.
Data visualisation means using the right
chart to get your message across. It also
usually involves removing unnecessary chart
components to reduce visual “noise”.
FORMATTED TABLES
This is a feature that has been around for
many versions but has slipped under the radar
of many accountants. I think because the word
“format” is on the icon it has been ignored
as a formatting feature. The Format As Table
button on the Home ribbon instructs Excel to
treat a table like a little database. It is a data
feature, not a format feature. It adds a lot of
extra data functionality to the table. I covered
this feature in detail in the August 2018 article
and video.
Many of Excel’s data features require the
use of a formatted table.
FUNCTIONS
These are the 20 functions I believe
accountants should learn. If 365 appears after
the name, it means you need Microsoft 365
(subscription version) or Excel 2021 to use
the function.
SUM – you probably think you know the
SUM function. Do you know what a 3-D
SUM is? Can you do one? In the latest Excel
version, the SUM function can perform all the
calculations that SUMPRODUCT can perform.
It is now more flexible than SUMIFS.
SUMIFS – this multi-conditional SUM can
include wildcards characters in its conditions.
IF – make decisions with a function.
AND – review multiple conditions and return
a single TRUE or FALSE.
OR – review multiple conditions and return
a single TRUE or FALSE.
XLOOKUP – 365 - a flexible more robust
replacement for VLOOKUP and even INDEXMATCH.
INDEX – the all-round flexible function that
works well with tables and dynamic arrays.
MATCH – the function that adds flexibility to
INDEX and other functions.
TEXT – the function that allows you to format
dates and numbers to use with text.
IFERROR – handles all formula errors except
the new #SPILL! error.
INDIRECT – the function of last resort - it can
provide solutions that no other function can
even attempt.
COUNTIFS – conditional counting can be
useful in many situations
EOMONTH – easily find the last day of a month.
EDATE – adds months to dates.
TEXTJOIN – join ranges of text together easily.
UNIQUE – 365 – list all the unique entries
from a list – great for creating drop-down
lists.
SEQUENCE – 365 – create a sequential list of
numbers that can drive budgets and financial
models from a single input cell.
FILTER – 365 – a formula that returns a filtered
list based on a data table.
SORT – 365 – a formula that returns a sorted list
based on a data table or another function.
LET – 365 – allows the use of variables within
formulas. Can reduce the length of complex
formulas.
DATA VALIDATION
This feature on the Data Ribbon tab is an
often-overlooked one that can make your
spreadsheets more useable and reliable.
You can use Date Validation to limit the
dates, numbers and text that can be entered
in a cell. It is commonly used for in-cell drop
lists.
POWER BI
Power BI takes Excel’s Power Query and
Power Pivot and adds a powerful Dashboard/
Reporting engine overlaid with robust security
and easy sharing capabilities.
Power BI is the likely future of many
existing Excel reports.
The Dashboard features are amazing
and can take reports to another level
of interaction, plus they can drill down,
something else Excel doesn’t have.
If you have not tried Power BI, I highly
recommend you download the free desktop
version and start playing with it.
The INTHEBLACK companion post will have a
video demonstrating some of these features.
intheblack.cpaaustralia.com.au May 2022 69