Microsoft Excel fundamentals

In Microsoft Excel, pivot tables are used for summarising, analysing and visualising datasets efficiently. The main parts include selecting data, inserting a pivotable and selecting the fields for analysis.

Pivot tables are useful for data analysis for several reasons:

It is easy to summarise data using functions such as SUM, COUNT, AVERAGE, MAX, MIN (very effective for quantitative data).

Data can be grouped based on dates, numbers or categories clearly showing how data changes based on the group. Linked to this, the data can be sorted and filtered specific for the user and audience, for example, product sales by region or year and sorted by sales to see the regions of highest revenue.

Calculated fields can be used to create columns via calculations. For example, this could be percentage profit (= ((revenue – costs)/revenue) * 100).

Lastly, pivot charts are essential for generating visualisations and dashboards to present data. There is a wide range of visuals to pick from such as a line chart for time-series analysis or a column chart for trends in sales. Pivot tables can be filtered using slicers which affects visualisations making it an interactive experience, therefore a few visuals can be used to show a wide range of hierarchical data based on the slicer settings selected.

VLOOKUP is a vertical lookup and HLOOKUP is a horizontal lookup which are both used to search values in a table to then return matching data.

VLOOKUP searches for a value in the first column of a table then returns data from a specified column in the same row.

Here the value 102 is the lookup value, A2:C4 is the table range to search for, the 3 is telling VLOOKUP to search the 3^rd column for a value and the FALSE means an exact match is required.

=VLOOKUP(102, A2:C4, 3, FALSE)

HLOOKUP searches for a value in the first row (instead if the first column in VLOOKUP) and then returns data from a specified row in the same column.

Here HLOOKUP is searching for the string “Feb” in the table range of A1:D3 and gets the data if found from row 2, with the FALSE meaning we want the exact match.

=HLOOKUP(“Feb”, A1:D3, 2, FALSE)

XLOOKUP however is a more powerful and flexible version of both VLOOKUP and HLOOKUP where XLOOKUP supports vertical and horizontal lookups along with exact and approximate matches.

Here XLOOKUP is searching for the value 104, within A2 to A4 but then looks in C2:C4 to find the matching value in the table. The string “Not Found” is returned if no match is found. The 5^th variable is the match mode where -1 is exact or the next smaller match (where 1 is the opposite, 0 is exact match and 2 is a wildcard match)

=XLOOKUP(104, A2:A4, C2:C4, “Not Found”, -1)

Here the setup is similar to the previous XLOOKUP example, but the 6^th variable provided is the search mode. Where 1 is first to last, -1 is last to first, 2 is binary search ascending and -2 is binary search descending.

=XLOOKUP(202, A2:A100, B2:B100, “Not Found”, 0, -1)

NOTE: A wildcard match is when you do not know the exact text but want to match a pattern. * is match any number of characters (Apple* matches Applesauce and Apple pie), ? matches exactly one character (Sm?th matches Smyth but not Smithe) and ~ searches wildcard characters as non-wildcards (e.g. like *)

There are multiple strategies in Excel to clean data:

1. Remove duplicates: This helps reduce the dataset size and prevents the anomalous results from duplicate values in any analysis.

To find any duplicate values =COUNTIF(A:A, A2) > 1 (returns true for any duplicates) and use the Remove Duplicate Excel button.

• Trim extra spaces: Removes any extra spaces around values which can interfere with sorting and filtering.

=TRIM(A2) This removes extra spaces but not single spaces between words.

• Remove any non-printable characters: Hidden or special characters can interfere with sorting and filtering.

=CLEAN(A2) This removes any non-printable characters.

• Convert data into the correct type or case: Aids in future analysis

=UPPER() Converts text to UPPERCASE

=LOWER() Converts to lowercase

=PROPER() Capitalises Each Word

=DATEVALUE Converts text to date

=TEXT(A2, “DD/MM/YYYY”) Forces text into a date format

=VALUE(A2) Converts numbers into text

• Split data into columns: If data is combined like a full name, splitting the data might be easier to analysed.

=TEXTSPLIT(A2, “, “) Splits text by a delimiter or use the Text to Columns Excel button.

Data can also be extracted using the following functions:

=LEFT() Extracts the set first characters

=RIGHT() Extract the set last characters

=MID() Exact from the middle from two points

=SEARCH() Find the position of a character

• Find and replace errors: If there are any errors such as #N/A, #VALUE! or #DIV/0!.

=IFERROR(A2, “Missing Data”) This checks the cell and if there is an error, replaces it with the string “Missing Data”

• Remove blank rows: removes empty rows which still increase the size of the dataset.

This can be done by using the Filter or Go To Special Excel button to delete these blank rows.

NOTE: Many of these strategies can be performed in Power Query.

Conditional formatting means Excel automatically highlighting cells based on specific criteria such as a threshold, duplicates or dates within a range.

By using the Conditional Formatting Excel button, cells can be under some rules such as when a cell has a value greater than 500, the cell becomes a different colour, all cells above 500 would show a red cell for example. A gradient colour scale could be used instead from highest to lowest.

A custom rule could be if =B2<300, if a specific cell is smaller than 300 then the cell changes formatting.

Lastly, =$C2=”Pending”, this means that this C2 column “Status”, if it equals “Pending” a colour of the cells would be changed and highlighted.

There is an Excel button called Data Validation which sets different types of restrictions such as whole number, decimal, dropdown list, date, time or a custom formula like min/max values.

A custom rule =MOD(A2,2)=0 means that only even numbers are allowed in these cells.

Another rule =ISNUMBER(SEARCH(“@”, A2)) means that the cells must contain @ for email addresses.

Macros can be setup in a few ways and must be enabled in Excel documents. In the Trust Centre Settings, the Macro Settings and Enable All Macros.

A macro can be recorded such as a FormatReport one. For example, start recording and then apply formatting such as bold and font colour with some data sorting and apply conditional formatting, then stop recording. This can then be run anytime to perform the same repetitive action. By inserting a button shape, this macro can now be assigned to activate by the button.

This macro could be done in Visual Basic for Applications:

Sub FormatReport()

    ‘ Selects the data range

    Range(“A1:D100”).Select

    ‘ Applies bold formatting to headers

    Rows(1).Font.Bold = True

    ‘ Changes font color to blue

    Range(“A1:D1”).Font.Color = RGB(0, 0, 255)

    ‘ Adjusts column width

    Columns(“A:D”).AutoFit

    ‘ Sorts data by column B (ascending)

    Range(“A1:D100”).Sort Key1:=Range(“B1”), Order1:=xlAscending, Header:=xlYes

End Sub