Posts

How can we rank these students from highest to lowest scores? Well, one option is to sort the students by average score in descending order. This works fine, but it’s not dynamic. Let me undo these last changes and let’s try it out. The RANK function takes three arguments: number, ref, and an optional argument called order. This means the highest number will get a rank of “1”. This reference needs to beabsoluteso that it won’t change as the formula is copied down....

Here we have data that represents one month of sales for a camping store. it’s possible for you to see the store sells tents, backpacks, headlamps, and so on. I’m going to rename this field “Sales,” and set the number format to Currency. Now, what if I want to add an actual rank value to the pivot table? This is easy to do. The first step is to add the field you want to rank in the Values area....

Abstract Transcript Moving fields around in apivot tableis fun and even addictive. This is where the idea of Pivot comes from. Let’s take a look. Each time you move a field, the pivot table will rebuild itself to respect the new field configuration. The easiest way to move a field is just to drag it from one area to another. We can just drag the fields to swap locations. And drag them back again to restore the original orientation....

Abstract Transcript Pivot tables do not automatically update when the source data is changed. To update a pivot table, you gotta refresh it. Let’s take a look. This causes Excel to reload the source data and display the latest information in the pivot table. If the source data has not changed since the last refresh, the pivot table will not change. To illustrate how this works, let’s change some source data, and then refresh the pivot table....

In this workbook, we have a number ofExcel Tables. Let’s look at some ways you’re free to remove these tables. You won’t find a “delete table” command in Excel. If a table sits alone on a worksheet, the fastest way is to delete the sheet. For example, this sheet contains a table showing the busiest airports in the world. When I delete the sheet, the table is completely removed. On this sheet, I want to remove the Orders table and leave the summaries....

when you land data in a table, it’s easy to remove duplicates. Let’s go through some examples. In this first worksheet, I have a table with a list of US cities and states. Some of the entries are duplicates. In a table with headers, the header checkbox will also be checked. The states column now contains a complete list of unique state names. Now let’s look at some data with more columns....

Let’s take a look. Both borders and fills can be removed using menus on the ribbon. First, go for the cells you’d like to change. To remove all borders, select No Border from the Border menu. This command will remove all borders completely. To remove all fills, select No Fill from the Fill color menu on the ribbon. This will completely remove all fills, including patterns and gradients. Borders and fills can also be removed using the Format Cells dialog box....

However, you might manually change the name to almost anything you like. Lets take a look. Here we have a blank pivot table. Lets add the product field as a row label and the total sales field as a value. Notice that value fields are automatically renamed to show the pop in of calculation being used on the field. In this case, we get Sum of Total Sales. If we change the calculation to Count, the field is automatically renamed to Count of Total Sales....

But nested IF statements can be difficult to maintain and debug. Let’s look at how you might use theVLOOKUP functioninstead. Here we have the classic problem of assigning grades to scores. Let’s add another column that calculates the same grade usingVLOOKUP. The first thing we’ll do is build a table we can use to assign grades. We’ll need a column for scores and a column for grades. Now we can see the formula while we work....

Here we have data for the top 10 islands in the Caribbean by population. Let me insert a standard column chart and let’s look at how Excel plots the data. When Excel plots data in a column chart, the labels run from left to right to left. Now, what happens if I change this chart to a bar chart? At first, you might think that Excel changed the sort order of the axis labels....

Abstract Transcript Once aconditional formatting rulehas been created, it can be easily revised. Let’s take a look. Once a conditional formatting rule is defined, it can be revised or deleted. To see and edit a conditional formatting rule, select Manage Rules from the Conditional Formatting menu. By default the Manage Rules dialog box is set to the Current Selection. If not, switch the dropdown menu to This Worksheet. To edit the rule, select it in the list and go for the “Edit Rule” button....

In this example, I’m working with anINDEX MATCH formulathat has a problem. Each time I give a shot to enter the formula, Excel complains. To do that, just put a single quote or apostrophe in front of the equal sign. This causes Excel to stop trying to evaluate the formula and instead treat it as text. Later, when you have more time, you might simply reverse the process. Just remove the apostrophe, and continue working with the formula....

One way to save a workbook is to hit the Save icon on the Quick Access toolbar. you’re free to also save a workbook by clicking Save in the File tab. This works fine, but it’s a little out of the way. This will pop kick open the Save As dialog box. you’ve got the option to then enter a new name and choose a location. When you click Save, Excel will save the file and update the window title....