As we all know Excel is a very powerful tool, however sometimes where your data is spread across various spreadsheets and other files trying to summarize it all can be tricky. You can add various lookups to pull in data but when you have to do this every month it can become very time consuming.
Built into Excel you have two very powerful tools that not everyone is aware of, Power Query and Power Pivot.
When working with data to report on, we use the principle of ETL, Extract, Transform and Load.
- Extract – is where you select the data from your required source to work with
- Transform – gets the data into the correct format to perform analysis
- Load – loads the data into Excel ready to analyse
Power Query enables you to pull in data from different data sources and transform it. You can pull in different Excel spreadsheets, CSV files, SQL or Access databases, data from websites and many other formats. Once in Power Query you can then organise and tidy up your data to ensure it is in a transactional format. You can make sure it has a proper header row, no blank rows or rows or columns of data you do not need. You can even add additional columns for formulas or conditional data. You can append records from other tables onto your data and where applicable merge data into columns from other tables. When you do this, it links to your original data source and creates a series of steps. What this means is that if the original data source changes, it will go through your steps to transform and update the data without you having to do it manually each time. A real time saver and it reduces the risk of error as once you have it right you know it will be right each time.
Power Pivot lets you then report on the data. Once loaded into Excel from Power Query the first thing you need to do in Power Pivot is to create relationships in your data so you know which table is connected to which and by what. For example you might have an table with a list of employees and another containing training data for everyone. So, you would use something like Employee ID to connect the two tables together. Once you have built the relationships you can now build Pivot Tables, Pivot Charts and use the powerful features such as Slicers and Timeline filters to report on information from multiple tables.
Our new one day Excel Power Query and Power Pivot course introduces you to the powerful features of these tools. This can be run virtually over a number of sessions or face to face.
Read Next
Keeping up to Date With Excel – Advanced Statistical Analysis
Excel plays such a huge part in most people’s everyday work and yet most people only use a small fraction of its capabilities. The trouble is, with Excel 365, they are adding new features [...]
What Are Macros and Why Should You Use Them?
What Is a macro? A macro is shorthand for a programming term: macroinstruction. In the most basic terms, a macro is a way to automate simple tasks. They can be used in a variety [...]
Using Advanced Formulas and Functions in Excel
With everyone using Excel more and more in business, the level of skill and knowledge required has increased hugely. Just being able to create a simple spreadsheet and input data is often no longer [...]
The Digital Skills Gap – Don’t Mind the Gap, Bridge It
We’re surrounded by technology; immersed in it so completely that most of us barely even realise it’s there (although we certainly notice when it’s not). Yet, it seems that despite our growing dependence on [...]
How to Make the Most of Your Digital Skills
It would be hard to think of a role that exists today that doesn’t require the use of technology in some form. The evolution of the digital realm has been so rapid that it [...]
New Course Announcement
We are excited to announce the launch of a new course and to share some updates to our range of Excel programmes. With Excel being such a key skill in today's business world, Underscore [...]