Managing data in Microsoft Excel

The world of Excel and data - course introduction

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

Structuring your data

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.

Unit introduction

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.

Freeze panes - and the equivalent for printing

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.

Splitting the window

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.

The watch window

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...

A mini-dashboard to highlight issues

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.

Exercise - creating a mini dashboard

Exercise review - creating a mini-dashboard

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

Unit review - what have we learned?

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

Unit introduction

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.

Custom sorting

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.

Simple filtering

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.

Advanced filtering

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!

Functions that do the job - a sneak preview

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!

Unit review - what have we learned?

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

Unit introduction

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.

Removing duplicates

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.

Working with Data Types

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.

Grouping and outlining your data

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.

Excel's subtotals tool

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.

Exercise - Working with Excel's data management tools

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.

Exercise review - working with Excel's data management tools

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.

Unit review - what have we learned?

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

Unit introduction

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.

A review of Conditional Formatting essentials

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.

Formatting entire rows when your condition is met

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.

Creating responsive forms with conditional formatting

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.

Exercise - Using Conditional Formatting

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.

Exercise review - using Conditional Formatting

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!

Unit review - what have we learned?

Unit introduction

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.

Creating data tables

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?

Adding new columns to a data table

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!

Structured references

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.

Getting rid of data tables

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.

Unit review - what have we learned?

Unit introduction

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.

Linking to other Excel workbooks

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.

Connecting to Microsoft Access

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.

Connecting to SQL

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.

Transforming data with Power Query

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.

BONUS LESSON: Getting SQL Server installed

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.

Unit review - what have we learned?

Unit introduction

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.

Controlling data quality with data validation

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.

Using the data entry form

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.

Creating your own data entry form - part 1

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.

Creating your own data entry form - part 2

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.

Unit review - what have we learned?

BONUS - If you're doing databasing...

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...

Unit introduction

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.

Naming your ranges

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.

Using aggregate functions with named ranges

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.

Database functions

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.

Lookup functions - VLOOKUP and HLOOKUP

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.

An alternative to VLOOKUP - INDEX and MATCH

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.

An even better alternative (maybe) - XLOOKUP

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.

Exercise - using functions with datasets

Your chance to tackle another challenge!

Exercise review - using functions with datasets

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.

BONUS – Handling the inevitable errors

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.

Unit review - what have we learned?

Unit introduction

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.

Introduction to array and spill functions

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

Array - or CSE - functions

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!

Returning results to more than one cell

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.

Returning the array functions results to another function

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.

Spill functions

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.

Referring to the results of a spill function

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...

Exercise - using data-driven functions

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.

Exercise review - using data-driven functions

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

Unit review - what have we learned?

Unit introduction

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

Creating PivotTables

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?

Grouping data in a pivot table

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.

Creating drill-down pivot tables

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.

Creating custom fields in a PivotTable

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.

Creating pivot charts

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.

Outputting multiple pivot tables from a master table

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.

Exercise - creating pivot tables

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?

Exercise review - creating pivot tables

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.

Unit review - what have we learned?

Unit introduction

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.

Scenarios

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.

Adding developer controls - part 1

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.

Adding developer controls - part 2

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.

Making a clickable process map

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.

Exercise - Creating an interactive dashboard

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

Exercise review - creating an interactive dashboard

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.

Unit review - what have we learned?

Unit introduction

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.

Sharing your file

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.

Controlling access with passwords

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.

Protecting sheets and setting permissions

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.

Adding notes and comments

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.

Viewing the revision history

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...

Unit review - what have we learned?

Unit introduction

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.

File size issues

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.

Slow updates

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.

Limited functionality

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.

Unit review - what have we learned?

Roundup - what have we covered in this 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.

Where next?

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