The cell reference the top left the pivot table

Manage data using Excel tables

Selecting transcript lines in this section will navigate to timestamp in the video

- [Instructor] Excel tables let you store and summarize your data in a flexible and efficient manor. You can greatly extend the capability of Excel tables by creating pivot tables and pivot charts. Which allow you to rearrange and summarize your data quickly. In this movie I will show you how to create pivot tables and pivot charts. My sample file is the create pivot Excel workbook. Which you can find in the chapter one folder of your exercise files collection. Excel tables are the best way to store data for use in pivot tables. One reason for that is that your data is well organized, and you know that it's in columns or rows as they should be, and also one other aspect of working with an Excel table is that if you add or remove rows of data, then the pivot table will reflect that the next time it's updated. If you work with a standard data range, which is entirely possible, you would need to go in and update the data source definition. The range of cells that contain the data. With Excel tables, the next time you refresh, it happens
automatically. I'll go ahead and create a pivot table first. I'll click any cell inside of the table that I want to use, and then on the Insert tab I will click Pivot Table. If you click Recommended Pivot Tables, then you'll see a list of pivot tables you can create, but in this case I'll take you around the long way. If I want to create a pivot chart at the same time, then I could go over to the charts group and click Pivot Chart, and that will create both a pivot table and a pivot chart at the same time, but let's start with just the pivot table. I'll go ahead and click the Pivot Table button. The create pivot table dialogue box appears, and I can select a table or range. In this case it's table one, which is correct, and I can select a destination. In this case I do want it to go to a new worksheet, but if I wanted to, I could put it on an existing worksheet and then I would just need to specify the location. That's all I need to do here. So I will click okay, and I have created my pivot table. Over on the right, you can see that I have the pivot tables field list, or the pivot tables fields pane. If for some reason that doesn't appear, you can click any cell in your pivot table, and then at the far right side of the analyze tab, make sure that you click the Field List button, and that will bring back the pivot table fields pane. From here we can add fields to the report. I have four areas. I have columns and rows, which will provide the structure. Values which will provide the details for the data that make up the pivot table, and finally filters, which doesn't change the organization of the pivot table, but does allow you to affect what values appear. Using filter fields is beyond the scope of this course, but you can get great information on it elsewhere in the Lynda.com library. So let's start out. I will add room type to the columns area. So I have Piccadilly and Cambridge, and let me change the zoom level. I'll go up to 180, and then I'll drag year to rows, and then below year I will drag quarter. So what I've done so far is create the outline for my pivot table, and you can see that I have my room type here. So I have Piccadilly and Cambridge. The year 2015 and 2016, and each of those years is broken down by four quarters. That reflects the structure that I created over here on the right in the pivot table fields pane. Now I can drag revenue to the values area. So I'll just click the name of the field, and drag it down

to values, and you can see that I have the values inside of the pivot tables. Let's say that I want to rearrange the data. So for example, instead of having the rooms in the rows area, let's say that I want them below the year. Say between year and quarter. To do that, I would drag room type from the columns area to the rows area, and place it between here and quarter, and you can probably see that there's a very faint bluish gray line that appears between the year and quarter. That indicates the target or the destination where room type will end up when I release the left mouse button, and when I do that, you can see that I have Piccadilly by quarter for 2015, followed by Cambridge also by quarter for that year, and 2016 down below as the same data, along with a grand total for all the data, and then also sub totals for Cambridge and Piccadilly for 2016, and above Cambridge and Piccadilly for 2015, plus the yearly totals. If I want to filter the data in the pivot table, I can go over to the PivotTable Fields pane, and then hover over the field by which I want to filter. In this case I'll do quarter. Go over to the right edge, where there's a downward pointing black triangle. Click the triangle, and you can see that I have a number of filter options. In this case, I think it makes the most sense to filter based on selection. So I'll use the check boxes here. So I'll clear one and two so that I'm only seeing data from quarters three and four, and when I click okay, I see the data there, and you can also see that the subtotals and the grand total have been updated to reflect only the data that is currently displayed within the pivot table. If I want to remove the filter, I can go over to the PivotTable Fields pane, and you see the filter icon here, indicating that the field is filtered. Click the downward pointing triangle again, and I'll click Clear Filter From Quarter, and all the data reappears. So far I've worked with data that is just in numerical form. If I want to extend it by creating a pivot chart, then I can click any cell in the pivot table, and then again on the Analyze contextual tab. Go over to the Tools group, and click PivotChart. Doing so displays a pivot chart, and it gives me a number of different types that I can create. So I'll just work inside of insert chart with a clustered column and click OK. And you can see that I have my data broken down by room type and by year, and if I scroll down you can see the indicators for the year, so the labels. I can also filter my pivot table. So if I only want to see data for year 2016, I can click the Year button here at the bottom left, clear the check box for 2015 and click OK. And the pivot chart updates so that it only displays data for 2016. If I want to undo that filter quickly, I can do it either by clicking here, and then click and clear filter, or I could have just clicked or pressed Control Z on the keyboard. You can also pivot, pivot chart fields. So for example, if I wanted to remove room type and just go by year, I could drag room type from the categories area up to the field list area, and then I see my data here, and I just have totals by quarter for the years 2015 and 2016. Pivot tables and pivot charts are extremely powerful tools. I encourage you to investigate them, and use them in your dashboards when you can.

Filter Excel table and PivotTable data

Selecting transcript lines in this section will navigate to timestamp in the video

That means when I copy the format to other cells, this cell reference will change. So I have C5 greater than or equal to the value in C1. That reference shouldn't change. The comparison value will always be in cell C1. So I'll make that an absolute reference. So I'll type dollar sign C, dollar sign one. Yep, that looks good. And now I can define my format. So I'll click the format button. And this time, I will go one shade darker. If you're not on the fill tab, click it now. And then I'll click the olive green square, the second from the bottom, and click okay. So now I have my format, and I'll click okay again. And it looks good because I

did not highlight or add the fill to cell C5. That's because the value in C5 is less than the value in C1. Now I want to copy the format from C5 to C6. So I will click cell C5, if it weren't already. Then on the hub tab, I'll click the format painter. And then click cell C6. And you can see that that applies the conditional format, and it is correct. It displays the cell with a green background because the value is greater than the value in C1. And I can do the same thing for cells C9 and C10. So I'll click the format painter again. I have cell C6, which has my conditional format applied, so I can do it this way. Format painter, and then select cells C9 and C10. And they are also correctly highlighted. The one great thing about using a formula-based value is that if I change my target by editing the value in C1, then the conditional format will be updated automatically. So, I will click cell C1, and instead of 1,200,000, I will type 1,250,000. So 1, 250, and then value that with three zeros. And enter. And you can see that the format has been applied based on the new value. C9 and C10 are both over 1.25 million, so they are highlighted with a green background. And C5 and C6 are not, because the numbers there are less than the value in C1. These types of conditional formats are extremely powerful. If you use a formula-based conditional format, make sure you get the copying right and you use relative and absolute references as required.

when you're making an icon set is that you always want to change the type first because it changes the value back to zero. With that in place I can set my threshold for outstanding performance. I will edit the green light's value. And let's say that any month where I have performance of above 1,250,000 is green which is the highest possible level in this case. So I have 1,250,000. And let's say for yellow that I want anything above 1,115,000. So I'll go down to the second row and I will change the type from percent to number and then I will edit the value. So I have 1,115,000. Everything looks good. So there I have one million two fifty, one million one fifteen and anything below one million one fifteen will get red. I'll click okay. Click okay again to close the rules manager. And there I see my new format. Picadilly and Cambridge are both over 1.25 million so they get green. That's for 2015. Whereas in 2014, Picadilly was mediocre performance and Cambridge was below the acceptable limit. Icon sets are very popular. If you use balance scorecards or other types of dashboards, then you might have seen this type of indicator before. It's very familiar to managers who've worked with dashboards before. And if this is your first exposure to it, you should get used to seeing it. It's used a lot.

Summarize data using charts

Create sparklines

Selecting transcript lines in this section will navigate to timestamp in the video

- [Instructor] In many ways, building a dashboard is easy. All you need to do is open up a worksheet and start adding elements to it, and there you have a dashboard. The best dashboards, however, are the result of planning. In this movie, I will give you one example of a process you might follow for planning the outline or design of a dashboard worksheet. My sample file is the blank sketch workbook, and you can find it in the chapter two folder of your exercise files collection. Again, what I would like to do is to just get a brief outline or idea of the way that I want my dashboard to be laid out. Many of you might prefer to do this with some colored pencils and paper, and that is a wonderful way to do it. It's easier for me to demonstrate the process in Excel, so I'm doing it here with shapes. Let's say that on the left side of the dashboard, I want to have specific performance data so individual values. If I want to mark where those elements will go, I can go to the Insert tab and then click the Shapes button and then select the shape I want to use. When I'm doing this sort of work, I typically choose the rectangle, so I'll click that. And then, I can draw my first shape. I'll just do that here. And let's say that it takes out about this much room, and then I'll change the style to something that I find a little easier to read. And then I can just type the text, and this is an indicator of what would appear inside of this area. Let's say that we have average daily rate by city, and I'll just resize here. Actually, I'll make that a little bit larger. Let's say that I have about 10 cities, and let's say that I spelled daily correctly. There we go, so there would be average daily rate by city, and then for the next area, I'll just click the shape again. And I'm still on the Format contextual tab because I was drawing. So I'll click there and then I'll add another element here, and I'll be doing the same type of data, so I'll just click the same style. And here, let's say that I have average room service revenue, again by city. And then, at the bottom, I would have another shape, and I scroll down a little bit. I'll scroll back up, make sure it all fits on one page. It does. I'll change the format, and let's say that this is guest count versus previous year. And I'm typing with initial caps, but obviously you can do that any way you want. So these would be numerical values. We have average daily rate by city, average room service revenue by city, and guest count versus previous year. Let's say that I also want to have some charts, perhaps the amount of money spent by each of my four departments. So for that, I would create a chart, and I'll make that a different color of shape, so I'll just draw here. And I'll make it a little bit larger. Let's say it's blue with white lettering, and let's say chart showing departmental expenses by month or quarter. And if I want to have an option, I will need to make that a pivot chart. So let's say, actually make that a little bit larger, so I'll resize the shape. And then, let's do one or two more. Let's say that I want to have another chart, which I'll draw here. And let's call that a line chart showing trends for city occupancy rates for the last two years. Then, we could also do some key performance indicators using conditional formats. So I'll create another shape and we'll do those here. And I'll just do a block so I have multiples, and I'll change the style to something a little bit different. Let's make this orange with white, and then let's say key performance indicators using icon set conditional formats for criteria to be determined. We're looking good so far. Let's say that I can't think of anything else to add. I'll start looking around and thinking about how to maybe balance things out. Let's say that I slide the key performance

indicators using icon sets down here to the right. That's good, except that if they're the key performance indicators, I probably want to have them at the top. So I'll drag that shape up, I'll drag these two shapes down, move this over to the left, and then I'll bring average room service revenue by city to there, average daily rate by city here, and then I'll drag guest count versus previous year here. And I'll just center these a little bit better so that everything on the screen is laid out reasonably well. So what I've done is to create a layout that reflects the information that I want to show in my dashboard. Using shapes is a great way to do that, but again, if you can do this by hand on a piece of paper using colored pencils to indicate the different types of data that you want to summarize, that's a great way to work as well.

Link cell contents to a shape

Selecting transcript lines in this section will navigate to timestamp in the video

Selecting transcript lines in this section will navigate to timestamp in the video

- [Instructor] One of the benefits of summarizing data using a pivot table is that you can refer to a pivot table cell, including subtotals and grand totals, in your dashboard. That way you get all the benefits of a pivot table with the compact data display of a dashboard. In this movie, I will show you how to link to a pivot table cell and also a cool trick that allows you to display the data that you want from a pivot table just by changing the value in one cell. I will work with the link to pivot workbook. And that's a sample file that you can find in the chapter two folder of the exercise files archive. First I'll just create a link to a pivot table cell. I'll create it in cell B3 which for me is already selected. If not, please click it now. Then type in the equals sign. And then go to the RoomPT worksheet. I'll click that sheet tab here. And this is the pivot table that I want to work with. I'm looking for the grand total of all room service revenue from the year 2015. I'll go to cell F5 and click it. And that identifies the cell. If you look up in the formula bar, you will see that rather than just giving a link to cell F5, I have a get pivot data formula. So the function is get pivot data. That tells Excel to look into a pivot table. And then there are various arguments which tell it where to look. The first argument is revenue. That's in double quotes. And that is the name of the field that contains the value that will be displayed. Next we have RoomPt which is the name of the workheet followed by an exclamation point and then A3. A3 is the cell reference at the top left of the pivot table. And if you look in cell A3, you will see that it says sum of revenue. So that's the first cell currently. Next we have year and that is the name of the field representing the row label of 2015. And then 2015 is, of course, the value that we're looking at. So we look across 2015 to the grand total. We're not looking at quarter. If we wanted to look at one of the individual cells, we would see a quarter value. And we see 475,579. With all that selected, I will press enter. And we get our value on the worksheet. Now heres something that's very cool. You can use the value in one cell to control what appears in your get pivot data formula. So I'll click cell B3 and press control+c to copy the formula, then click cell B6 and press control+v to paste. So you can see that I have exactly the same thing. But I want to do now is to create a data validation list in cell C6 that will list the values 2015 and 2016. That way I can flip between the one that I want to see. C6 is already selected. I'll go to the data tab and in the data tools group, I'll click the data validation button. In the data validation dialog box, I'll go to the settings tab. If it doesn't show up automatically for you. And then, I'm going to click the allow list box's down arrow. And click list. Then I'll click in the source box. There's two ways that I can do this. The first is if I had a list of values already in the worksheet or anywhere in the workbook, then I could click the collapse dialog box button and then select the cells. But in this case what I want to do is just type in my source or type in my values. So I'll type 2015, 2016. And those are the two values for which I know I have data within my pivot table. And I'll click okay. And my data validation rule has been applied. You can see it here. I haven't selected the value yet but you see that I have the down arrow at the right side of cell C6. And when I click that down arrow, I get values 2015 and 2016. So if I click 2015, that value appears. Now if I edit the get pivot data formula in cell B6, I can tell it to look at the value in cell C6. What I'll do is I'll go up to the formula bar and again, B6 is selected, and I'll the edit the 2015 to the right of year so that instead of saying 2015 it says C6, my cell address. Then I'll press enter. I get the same value but now when I click cell C6, click the down arrow and select 2016, the value updates. That is a very cool way to display the data that you want inside of a dashboard. If you work with pivot tables, I think you'll find that this method is extremely useful.

Selecting transcript lines in this section will navigate to timestamp in the video

- [Instructor] When you create a worksheet in Excel, it comes with a standard zoom level, which refers to the general size of objects on the screen. Excel also shows gridlines, which are marked boundaries between cells by default. If you want to change the amount of information displayed on a worksheet at a time, or turn gridlines on or off, you can do so quickly. You can also if you want hide all or part of the ribbon to give yourself even more screen real estate. I'll demonstrate these techniques in this movie. My sample file is the GridlinesAndZoom Excel workbook, which you can find in the Chapter Two folder of your Exercise Files collection. I am on the Dashboard worksheet of the workbook, and you can see that what I have on the screen doesn't take up all the room that's available, so what I could do is to resize the pivot chart here showing the different departments and what their quarterly expenses are, or I can zoom in. If I want to zoom in, that is to increase the magnification of the images on the worksheet, I can do it a couple of ways. First, I could go to the View tab on the ribbon, and then in the Zoom group, I can click the Zoom button to change the magnification. For example, to 200%, and click OK, or if I wanted to