Oz du Soleil shows how to close gaps in a column of data by using Get & Transform in Excel 2016. This video shows both fill up and fill down.
- [Instructor] This next Get & Transform feature fill up, fill down is a lifesaver for closing holes in columns. If you want to follow along, use the exercise file titled Fill Up Fill Down. Here I'm an admin who needs to provide volunteer data about some time that we spent as company, going out in the world and doing good. Now here is a report that I've gotten. Got the Riverside office. City Center office.
And then the areas of volunteerism. So we've got adult literacy. We've got health and wellness twice. Something that we had to like to know is by office, how many hours were volunteered in each category? That answer is not so easy to get at because of the holes in our columns. And these totals that are in the way. The key to this is going to be fill up, fill down. Let's get at it. With my cursor in the data set.
We'll go to data, from table. Now let's see if Excel guessed the proper range. Did it include all of my data? Yes it did. Beautiful. Click okay. There's the data. Now, I'm going to leave this column highlighted. Right-click. Fill. Down. Ha. Look at that. It says Riverside all the way down, until it changes to City Center and then it finishes with City Center.
Beautiful. Now, we'd like to take the volunteerism column and have that filled up. We'd like to have adult literacy there in row four go up. Right? And health and wellness go up, until it hits data literacy. Here we go. Highlight the column. Right-click. Fill. Up. Beautiful. Now to get rid of these total rows, notice that null is our trigger.
Let's get that out of there. Unselect null. Okay. And let's close and load this to the workbook. Lordy, lordy look at that. Okay, let's make a pivot table so that we can answer our question of by office, how many hours volunteered in each category? Insert. Pivot table. New worksheet. Close this pane.
We've got office. Let's put that in columns. Total hours in values. Volunteerism category in rows. And that's exactly what we wanted to know. Now we can see that the Riverside office volunteered 57.75 hours in health and wellness.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins