The Top 3 Things Need to Know to be an Excel Ninja

Pivot Table, Lookups & De-duplicating Data

  1. Pivot Table

This is an amazing tool that summarises data into any format you need

Whether you just need a total by a category or data by month

This will select your data & use wizard to put it in a table for ease of reading or insights

The next great thing from pivots is you can slice it & graph from it

Also, when the raw data gets refreshed, just refresh the pivot table & everything will flow through

  • Lookups

This is a function (formula) that matches data across different tables

You can just use =lookup() or use more specific vertical lookup =vlookup()

There is also a horizontal lookup = hlookup() but this is rarely used & a bit finicky

In 365 there is now =xlookup which combines all of the above & uses the logic of =sumif()

  • De-duplicating Data

Sometimes we work with data that has duplicated data in it

There is a lovely menu option Data -> Remove Duplicates

All you need is to have one cell selected in the data & Excel will do its magic very quickly

At the end you get a message box explaining how many rows were removed

This will only remove exact duplicates

If you can conquer these then you will be amazing

Chishiki

knowledge, understanding, information
Excel Tips

Excel Super-Powers

It seems Microsoft has been inspired by Marvel by creating functionality called POWER It has Power Query, Power Pivot & Power BI All 3 have

Read »
Excel Tips

When to use =sumif()

There are a couple of ways to add up your dataThe obvious one is =sum()The other is adding up individual cells, handy if there are

Read »

Kaigi o tehai shimashou

let's arrange a meeting