From the course: Excel: Power Query (Get & Transform) (2020)

Fill up and fill down

From the course: Excel: Power Query (Get & Transform) (2020)

Start my 1-month free trial

Fill up and fill down

“

- [Instructor] I'm an admin, and our company recently went out in the world and did a lot of good. We volunteered in various areas, adult literacy, technology, etc, and now the company presidents wants a simple summary how many hours volunteered by office by volunteerism category. That's all we want, not all of these details. But you notice there are holes in our categories because River Side starts at Melanie and goes down to Cynthia, and then adult literacy in row 10 should go up to Melanie, so we can't do any summaries with this. What we've got to do, take this into Power Query, do some fill up and fill down. Cursor in the data set. Data from table/range, OK. First, let's do the fill down so we can fill in the holes in the office column. It's already highlighted, right click, fill down, all right. And River Side stopped where City Center started and went all the way to the bottom. Beautiful. Now volunteerism category. Highlight that column, right click, fill up. Beautiful. We don't need those totals rows like where that 53.5 is. That's a total, get rid of it. So we're going to go to the employee column, down arrow, uncheck nulls, get rid of them. This is what we needed. So let's close and load, and let's do a quick pivot table. Insert, pivot table, new worksheet. Let's close this pane. Drag office into columns, drag volunteerism category into rows, hours volunteered into values. Now we can see the River Side office did 35 hours of volunteer work in the area of health and wellness.

Contents