Managing data in Microsoft Excel

  • Courses
  • Managing data in Microsoft Excel

Premium course

Intermediate

Video/Text

Video/Text

Microsoft Excel

104 Lessons

0%

In this course, we'll give you the skills you need to get more from your data - entering it, validating it and reporting it. We'll connect to external data in other spreadsheets and in SQL Server and Microsoft Access and we'll look at advanced techniques in creating great dashboards.

Do you wish your spreadsheets performed better, opened faster and updated more quickly?

Have you ever thought "I wish there was a way to..."?

Do you sometimes struggle to figure out the best way to set up your files, especially when they contain loads of data?

Then this is the course for you!

With our second-to-none teaching methods based on over 20 years of consulting with some of the world's biggest organisations, we'll show you how to make your data work for you!

On this course, you'll get

Plenty of real-world exercises to work through to give you hands-on experience

  • After every exercise, we'll show you how we'd tackle that challenge for our clients
  • Samples, templates, cheat-sheets and quick-ref guides to take away and use in your work.
  • And no "fake" training files which are too simple don't reflect what you're actually faced with at your desk!

So, don't delay! Sign up now and let's get started!

Ready to get started?

Managing data in Microsoft Excel

Introduction

2 Lessons

Premium course

Welcome onboard! Before we dive into the nitty-gritty, let's start with an overview of managing data in Excel, and see what we're going to cover in this course

Video lesson

Premium course

Sometimes your data will come from elsewhere - databases or links to other sources. But sometimes you'll need to create the lists for yourself. In this lesson, we'll take a look at the basic guidelines to help you get the data structured in a way that will make it simple and reliable to work with.

Video lesson

Data display options

8 Lessons

Premium course

A nice gentle unit to get us going! When you've got thousands - or hundreds of thousands - of rows of data, it can be a challenge to keep track of it all. In this unit we'll take a look at some of the tools which may help, and some ideas for how to look out for potential problems.

Video lesson

Premium course

Let's have a look at the tools for ensuring that you can see critical areas of data at all times - whether on your screen or in print.

Video lesson

Premium course

An alternative to freezing panes is to split your window. It's an under-used tool, but offers one or two really great benefits, as we'll see.

Video lesson

Premium course

When you need to keep an eye on the results of calculations whilst on a different sheet - or even in a different file - the Watch Window can be really helpful. Let's explore...

Video lesson

Premium course

Need to ensure that you've got the most recent data? Or that you're not suddenly seeing a nosedive in rows of data being reported? Let's create a mini-dashboard just to monitor your data quality.

Video lesson

Premium course

Premium course

Let's tackle that exercise together - I'll show you one way to go about it, and you can compare my results with yours.

Video lesson

Premium course

Let's take a look back at what we've covered in this unit.

Video lesson

Sorting and filtering

6 Lessons

Premium course

You're probably very familiar with the standard tools for sorting and filtering data within Excel. However, in this unit, we'll consider some more advanced use of these tools.

Video lesson

Premium course

Sorting your data from 1 to 100, or from A to Z, is straightforward. In this lesson, we take a look at some equally simple - but less commonly used - ways to sort data. Firstly, we'll look at sorting the data by custom lists, and then we'll look at sorting data from left to right.

Video lesson

Premium course

The tool used to be called 'AutoFilter' - and it really does more or less sort itself out. But there are one or two bells and whistles that have been introduced over the years, so let's take a look at some of these extras in the Excel filtering tool.

Video lesson

Premium course

There is a tool called the "Advanced filter" in Excel. It's rarely used, which is a shame as it offers some interesting additional functionality. Let's explore together!

Video lesson

Premium course

If you've got a subscription to Microsoft 365 then there's another way to sort and filter data... we're talking about Spill Functions in depth a little later, but we'll take a sneak peek at a couple of them here as they fit in so well!

Video lesson

Premium course

Let's take a look back at what we've covered in this unit.

Video lesson

Using Excel's data management tools

8 Lessons

Premium course

Excel has a whole set of tools to help manage data. None of them are difficult to understand, but sometimes they do more than is immediately obvious. In this unit, we'll make sure that you've got all these tools in your toolkit.

Video lesson

Premium course

In this lesson, we'll explore Excel's "remove duplicates" tool, and we'll see how it can be used within a table of data to leave just the rows you specifically want.

Video lesson

Premium course

If you're using Microsoft 365, then Excel has the ability to handle custom data types. There are a couple built in - for handling stocks and shares, and for working with geographical information, so we'll take a look at these, and later in the course we'll see how to create your own custom data types.

Video lesson

Premium course

If you have a large data set, it's quite likely that you won't actually need to see all of it all the time. A common solution to this is to hide columns - but grouping and outlining your data is often a much better approach. Let's look at how we do this.

Video lesson

Premium course

It's really easy to group your data into sections and add subtotals. In this lesson, we'll take a look at how you can add one or more sets of subtotals to your dataset.

Video lesson

Premium course

Now it's your turn to use the tools we've discussed to provide some analysis of major world cities and the countries in which they are located.

Text lesson

Premium course

Let's see how you did. I'll tackle the exercise I just set you, and you can see whether you approached it in the same way - and whether you got the same results.

Video lesson

Premium course

Let's take a look back at what we've covered in this unit.

Video lesson

Advanced use of the conditional formatting tools

7 Lessons

Premium course

Conditional formatting is a powerful and simple tool to get started with. But it can do a lot more than just make a cell go red if the value gets high. In this unit of the course, we're going to explore some of the more interesting capabilities of the conditional formatting tool.

Video lesson

Premium course

Let's make sure you're happy with the basics first. In this lesson, we'll consider the various ways of creating conditional formats, and the sort of formatting you can do when your conditions are met.

Video lesson

Premium course

It's very easy to miss a single cell's format changing in a large block of data. So, it's often better to format the entire row if, say, an order is delayed, or a parameter is exceeded. Let's look at how that can be done.

Video lesson

Premium course

How many children do you have? Three? Then let's show three "Child's name" fields. Two? Then let's only show two. If you say that you'd like to leave additional feedback, we'll show a field for that. These are just a couple of use cases of creating forms which respond to a user's input. No code needed - just more conditional formatting! Let's see how it's done.

Video lesson

Premium course

Now it's your turn to flex your new Conditional Formatting muscles! See if you can complete the challenge - and then we'll tackle it together.

Text lesson

Premium course

How did you do? Let's have a look at the challenge together and see whether you came up with the same solution - or even something better!

Video lesson

Premium course

Using data tables

6 Lessons

Premium course

Question: When is a range not a range? Answer: When it's a table! It's really easy to dismiss a table as just being data with some pretty formatting - partly because that seems to be how Excel sets it up to appear. But dig a little deeper and you'll discover some real power. In this unit of the course, we'll see just what's possible with a data table.

Video lesson

Premium course

Let's start at the very beginning (a very good place to start!). How do you create a data table? What options do you have when setting one up? And why would you even do it?

Video lesson

Premium course

Now we've got a table, we should take advantage of some of its cool features. For a a start, the days of filling formulas down 1000 rows (and hoping that's far enough) are gone... Let's look at why that is!

Video lesson

Premium course

You may have already noticed that when you create a formula that points to one or more cells inside a table, these formulas don't look like ordinary calculations. The references used to refer to a table are called "Structured references" and you need to understand how they work.

Video lesson

Premium course

They're really useful... but not necessarily the solution to everything. There may be times when you want to get rid of your data table - but keep its data. We'll explore how you do that, and what you may need to do to "clean up" afterwards.

Video lesson

Premium course

Connecting to external data sources

7 Lessons

Premium course

Your file isn't the only place where data resides. You (or your organisation) will almost certainly have data in other workbooks, of course. And you may very well have data in other types of resource - Access databases, SQL databases and so on. In this unit, we'll take a look at how you can get access to data stored elsewhere - and for files which are already linked to other data sources, how to see where that "elsewhere" is.

Video lesson

Premium course

The most common type of external link in Excel is undoubtedly one that connects your file to another Excel file. In this lesson, we'll consider how you create such a link, how Excel refers to other files, what to do when your link breaks, and how to find and manage links to other files.

Video lesson

Premium course

Microsoft Access is the database in the Office suite of applications. It's one of the most widely used databases in the world, so the odds are pretty good that at some point you'll want to connect to it. In this lesson, we'll take a look at how you connect to an Access database, and get data from its tables and queries.

Video lesson

Premium course

SQL Server is Microsoft's "other" database technology - the one designed for enterprise-scale data. Once again, Excel can connect seamlessly to it, so we'll look at how you would do this. We'll also look at how you manage existing connections, including breaking these links when you no longer need them.

Video lesson

Premium course

Power Query is, as the name implies, rather powerful. In fact, we could spend the entire course looking at how to use this tool. In our case, however, we're going to look at how to extract data from a SQL database, transform a couple of the columns, filter the data to restrict the rows we get back, and then get the results into our spreadsheet.

Video lesson

Premium course

In a number of the lessons in this unit, we've discussed getting data out of a SQL Server database. No doubt you've been impressed and inspired, and now you can't wait to try it out for yourself... only you don't have SQL installed. No problem - in this bonus lesson, we'll guide you through creating a default installation - for free - of SQL Server, and also look at another (nearly free) alternative - SQL Azure.

Video lesson

Premium course

Entering data

7 Lessons

Premium course

If you're using Excel to store data, it may be that in addition to importing data from other sources, you need a way of adding rows to that set of data one by one - new call logs, records of hours worked or whatever else. In this unit, we'll look at your options for doing this reliably.

Video lesson

Premium course

If you're entering data, it needs to be of good quality. Fortunately, Excel provides a data validation tool to assist with this. In this lesson we explore how to use it to best effect.

Video lesson

Premium course

In old versions of Excel, there was a data entry form to provide a convenient user interface to assist with data entry. Then it seemed to disappear. In this lesson, I'll show you how to dig it out again, and how to use it to enter and find data.

Video lesson

Premium course

Although Excel's data entry form is useful, it does have limitations. It can only enter data into one sheet, it doesn't do any form of validation and it can't show any form of feedback or related information. But with some clever formulas, there's nothing to stop you creating your own data entry form - no VBA or macros required! In part 1, we look at the form itself.

Video lesson

Premium course

In the second part of this lesson, we'll look at the formula needed to append data to your list - and at the calculation methods in Excel's options which need to be adjusted to get it to work.

Video lesson

Premium course

Premium course

As we've seen in this unit, Excel is a very capable list management tool, with great analysis functionality. But if you want to manage multiple lists, and create create powerful data entry and data reporting tools, it may be that there's a better tool...

Video lesson

Calculations and datasets

11 Lessons

Premium course

We've looked at creating, linking to and working with lists of data. We've briefly considered referencing cells within a data table. But now we're going to look at functions designed specifically for working with lists of data.

Video lesson

Premium course

If you've never heard of named ranges, then your life is about to change. If you have, well this lesson will provide a useful recap. When you want to work with a block of data, life gets SO much easier when you can refer to that block by a name, rather than by its address. Let's look at how you set up, and then manage and use, a named range.

Video lesson

Premium course

Now you've got some named ranges set up, let's see how you use them when using the simplest functions in Excel - the likes of SUM, MAX, AVERAGE and COUNT.

Video lesson

Premium course

There is a set of functions in Excel designed to work with lists, or databases. In this lesson, we'll see how DSUM, DMAX, DAVERAGE and similar functions work, and how they can be a useful tool in your armory for producing a dashboard.

Video lesson

Premium course

VLOOKUP (and its sibling HLOOKUP) is one of the most commonly used - and (if my experience or working with clients is anything to go by) most commonly misused - functions in Excel. Let's break it down and see exactly how to use it fully.

Video lesson

Premium course

VLOOKUP is great. But it does have its limits. In this lesson, we'll look at what those limits are, and how you can get around many of them by using a pair of functions - INDEX and MATCH.

Video lesson

Premium course

XLOOKUP is the VLOOKUP we've always wanted - it gives us power, flexibility and happiness. Well, most of us. In this lesson, we'll look at what it is, how to use it, and why you may not actually be able to.

Video lesson

Premium course

Your chance to tackle another challenge!

Text lesson

Premium course

How did you get on? Easy peasy lemon squeezy? Or a complete nightmare of impossibility? Hopefully neither extreme - but either way, let's look at the exercise together.

Video lesson

Premium course

VLOOKUPS and the other functions covered in this unit are great - when they work. But you'll often get errors. In this bonus lesson, we'll look at what your options are for handling these errors gracefully.

Video lesson

Premium course

Array functions and Spill functions

10 Lessons

Premium course

There are a number of functions in Excel (at least - in some versions of Excel) which not only work on ranges of data, but actually produce ranges of data as a result. These can be array functions or spill functions. In this unit, we'll look at both.

Video lesson

Premium course

What exactly are array functions? And what are spill functions? Let's consider the task ahead of us.

Video lesson

Premium course

When you want to create an array function, you need to learn a whole new way of entering your formulas. It's not as hard as it sounds - just a couple of extra keypresses. But knowing which keys they are seems like a good place to start this section of the course!

Video lesson

Premium course

If your array function is going to return results to more than one cell, you need to know where all these results are going to end up! Let's take a look at a couple of examples, and all will become clear.

Video lesson

Premium course

Rather than filling up lots of cells with the answer to an array function, it's probably more common to just pass those results on to another calculation. Let's take a look at how we do this.

Video lesson

Premium course

Like an array function only easier - a spill function returns results to a group of cells. Let's see how you do this with some really useful examples of spill functions - and what could go wrong along the way.

Video lesson

Premium course

How do you refer to the cells containing the results of a spill function, when you don't know which cells they will be? The answer is surprisingly easy...

Video lesson

Premium course

Now's your chance to put what you've learned in this unit into practice as you tackle a real-word example requiring several of the functions that featured in this unit.

Text lesson

Premium course

Let's take a look together at one way of tackling that last exercise.

Video lesson

Premium course

Working with PivotTables and PivotCharts

10 Lessons

Premium course

Pivot tables are one of Excel's most powerful features. In this unit, we'll explore the idea behind them, and how you can use them to create analysis of thousands of rows of data in mere seconds

Video lesson

Premium course

Let's start from the basics - how do you create a pivot table? What are the various "zones" in a pivot table? And how do you decide what to put where?

Video lesson

Premium course

Often it makes sense to show data in groups - by location, team, month, supplier or whatever is relevant to your organisation. In this lesson, we'll look at how you can group data together in a pivot table, using both manual and automatic grouping options.

Video lesson

Premium course

One of the features which makes pivot tables so powerful is the degree of flexibility they offer in how you present your data. In this lesson we'll take a look at how you can structure your data to allow users to double-click and drill down to the details underlying a summary.

Video lesson

Premium course

When you want to report on calculated values in a pivot table, it's often easiest to add the calculation to the raw data. However, it's also possible to add a calculation to the pivot table. In this lesson, we'll explore how to do just this.

Video lesson

Premium course

Just as a chart can bring information to the fore in a way that raw data may not, so a pivot chart can sometimes really make valuable information really pop out. Let's see how to create a pivot chart - and how the chart interacts with its underlying data.

Video lesson

Premium course

If you've got a field in the Pages area of a pivot table, this is essentially a three-dimensional table. In this lesson we'll explore a great trick to output the different pages which you now have.

Video lesson

Premium course

In this exercise, I'll you a set of over 100,000 rows of data. Can you pick out the key information from the forest of figures?

Text lesson

Premium course

With pivot tables, there really is no right answer - the analysis that one person comes up with may be completely different from someone else's output, and yet just as valuable. Let's look, then, at one option for finding out what was needed in this exercise.

Video lesson

Premium course

Creating interactive dashboards

8 Lessons

Premium course

In this unit, we'll take a look at what you can do to create interactive dashboards. Often people think that interactivity = VBA, but whilst we'll dip our toes into the world of macros, it's possible to add interactivity and flexibility without going down that route.

Video lesson

Premium course

Although in theory most cells can only contain one value at a time, there are various ways in which that rule can be bent. One such way is by using scenarios and the scenario manager.

Video lesson

Premium course

If you want to allow people to choose a value in a given range - say between 1 and 100 - then rather than getting people to type their value into a cell, adding tools such as scroll bars or up/down controls can provide real richness to the interface. These are known as developer controls, and in this lesson we'll look at how we add them and link them to your spreadsheet.

Video lesson

Premium course

Now we can add graphical controls to our interface, let's combine these controls with other tools we've seen to create a fully interactive dashboard.

Video lesson

Premium course

When you create a macro in Excel, you need to give people a way to run it. The Macros dialog box is one way, but there are several others which provide more flexibility in creating a great user interface.

Video lesson

Premium course

Now it's your turn to create a dashboard which is visually appealing and provides great functionality.

Text lesson

Premium course

The type of dashboard you created in the exercise will be a function of all sorts of factors - not least your sense of graphic design! But together, we'll create at least one solution to the exercise.

Video lesson

Premium course

Sharing your data

7 Lessons

Premium course

If you want to allow other people to work on your files, there are a number of things to consider. How will they access the file? Can they make unlimited changes? Who will have access? We will consider these factors and more in this unit of the course.

Video lesson

Premium course

The tools and options you have for sharing an Excel file vary considerably depending on your version of Excel. This lesson will focus on the sharing options which exist in Microsoft 365.

Video lesson

Premium course

In addition to sharing your file with specific people, you can also assign passwords to files to prevent people from opening it or to prevent people from saving changes without knowing the magic word. Let's see how it's done.

Video lesson

Premium course

As well as protecting the entire workbook, you also have more granular control available within Excel. You can protect a specific worksheet, and then control exactly what people can do on that worksheet.

Video lesson

Premium course

The ability to add comments to an Excel cell has been available for years. In current versions of Excel, however, these comments have been renamed to "Notes", and comments are rather more powerful. In this lesson, we'll investigate both options and see how they compare.

Video lesson

Premium course

If you and your colleagues make frequent changes to workbooks, it can be tough to see what's changed - especially as the "Track changes" option has now been removed. It is possible, however, to view a complete revision history of what's happened to your file. Let's see how to do it...

Video lesson

Premium course

Resolving problems with Excel data

5 Lessons

Premium course

Excel is great - but it can sometimes throw up problems, particularly when you're dealing with large files. In this unit, we'll take a look at some of the more common issues, and how you might look to resolve them.

Video lesson

Premium course

One of the common challenges you'll come across is large files - and there can be a number of causes for this, each with its own resolution. In this lesson, we'll look at potential causes and fixes.

Video lesson

Premium course

If you have a complex spreadsheet with tens or hundreds of thousands of calculations, every time you make a change, the updated calculation cascades through the whole file, and can make your file sluggish. But there are potential solutions to this - so let's investigate.

Video lesson

Premium course

Occasionally, you may come across a file with numerous buttons on the ribbon greyed out. There can be a simple resolution to this - depending on the cause.

Video lesson

Premium course

In conclusion...

2 Lessons

Premium course

Let's take a look back at some of the key take-aways from this course and see what you've learned along the way.

Video lesson

Premium course

You've come a long way - but where should you go from here if you have a reliance on data-heavy Excel files?

Video lesson

Follow

About the teacher

Andrew Richards

Andrew Richards has consulted and provided training for the world's leading companies - Marks and Spencer, Imperial College London, the Institute for Government and Net A Porter are among his clients. He's been running training courses for over 20 years for companies large and small as well as for government bodies, charities and healthcare providers.

Comments are closed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Pen