Transcript When working with historical dates in Excel, you need to be aware of two things: The first is that when you enter a 2-digit year, Excel will assign a century to create a valid date using a special rule. When the year is 29 or less, Excel will use the twenty-first century. When the year is 30 or greater, Excel will use the twentieth century. The second thing to be aware of is that Excel does not recognize any dates before Jan 1, 1900....
Transcript In addition to filtering a pivot table using Row or Column Labels, you can also filter on values that appear inside the table. Let’s take a look. Here we have an empty pivot table using the same source data we’ve looked at in previous videos. Let’s add Product as a Row Label, and Total Sales as a Value. Now let’s sort the pivot table by Total Sales in descending order....
Transcript Once you open one or more Excel workbooks, you’ll need to know how to get around. In this lesson, we’ll look at how to figure out where you are now, and how to get to where you need to go next. Let’s take a look. As you work in Excel, one of the first questions you need to answer is “where am I now?” You can always tell what workbook you’re in by looking at the name in the window title bar....
Transcript In simple cases, we either want our references to be absolute or relative, but there are times when we need both. Sometimes you want to copy a formula and have the column for the reference be fixed while the row changes, and vice versa. Excel has an easy way of handling this situation. Let’s take a look. Here we have a small table of data that represents widget sales over a 3-month period....
Transcript In this video we’ll look at how to use the MATCH function to find approximate matches. This is useful for things like determining a commission tier based on a sales number, figuring out a tax rate based on income, or calculating postage based on weight. Let’s take a look. In this example, we have a table of commission rates on the right and a list of sales numbers in a table on the left....
Transcript Excel contains functions that will let you extract the hour, minute, and second values from a time, and a function called TIME that will let you put them back together again. Let’s take a look. Here we have a set of random times in column B. First, I’ll add a formula to column C to pick up the time values in B and format them in General format, so you can see the raw time value....
Income statements are commonly shown in a combo chart, with columns plotting revenue and net income, and a line showing the profit margin as a percentage. You can see examples of this on Google’s finance pages . This kind of chart is easy to make in later versions of Excel by inserting a combo chart. The data used to create this chart is shown below: How to make this chart Select the data and insert a “combo chart” Select the send option - columns with line on secondary axis: The initial chart looks like this: Select revenue columns, then set series overlap and gap with Select legend and move to top: Select secondary axis and set units to major units to ....
Explanation At a high level, this example is about finding a minimum value based on multiple criteria. To do that, we are using the MIN function together with two nested IF functions : {=MIN(IF(day=I5,IF(tide="L",pred)))} working from the inside out, the first IF checks if the day is “Mon”, based on the value in I5: IF(day=I5 // is day "Mon" If the result is TRUE, we run another IF: IF(tide="L",pred) // if tide is "L" return prediction In other words, if the day is “Mon”, we check if the tide is “L”....
Explanation The LOOKUP function does an approximate match lookup in one range, and returns the corresponding value in another. Although the table in this example includes both maximum and minimum values, we only need to use the minimum values. This is because when LOOKUP can’t find a match, it will match the next smallest value. LOOKUP is configured like this: The lookup values come from column B. The lookup vector is entered as the named range “mins” (E5:E9) The result vector is entered as the named range “results” (G5:G9) LOOKUP behaves like this:...
About This Shortcut This shortcut will move the active cell right one cell when there are multiple cells selected. About This Shortcut This shortcut will move the active cell left one cell when there are multiple cells selected.
Explanation Working from the inside out, EDATE first calculates a date 6 months in the future. In the example shown, that date is December 24, 2015. Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the start date, with days = 1, and the range B9:B11 provided for holidays. WORKDAY then calculates the next business day one day in the future, taking into account holidays and weekends....
Explanation In this example, the goal is to retrieve employee information from a table using only a partial match on the last name. In other words, by typing “Aya” into cell H4, the formula should retrieve information about Michael Ayala. The VLOOKUP function supports wildcards , which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table based on typing in only part of a lookup value....
To list and count the most frequently occurring values in a set of data, you can use a pivot table. In the example shown, the pivot table displays the top Wimbledon men’s singles champions since 1968 . The data itself does not have a count, so we use a pivot table to generate a count, and then filter on this value. The result is a pivot table that shows the top 3 players, sorted in descending order by how often they appear in the list....
Explanation The goal is to calculate discounts on a per-item and per-quantity basis using the discount table at the right in the workbook shown. The purpose of the discount table is to allow each item to have its own set of discounts. Notice that Donuts have a different discount for a quantity of 24. The discounts for other items can be customized as well. This is a classic two-way lookup problem....
Explanation First, it’s important to note first that Excel dates are simply large serial numbers . When we check for a date with a formula, we are looking for a specific large number, not text . This formula is a basic example of using the COUNTIFS function with just one condition. The named range dates is supplied as the first argument, and the date in column E is supplied as the second argument for the condition:...
Transcript In this video, we’ll look at shortcuts you can use to group, ungroup, and work with outlines. In this worksheet, we have some basic data subtotaled by region and quarter. The shortcut for grouping rows or columns in Excel is Alt + Shift+ Right arrow in Windows and Command Shift K on a Mac. If you only have cells selected (not entire rows or columns) this shortcut will cause Excel to display the Group dialog box....
Explanation This formula first generates a rank value using an expression based on COUNTIF: =COUNTIF(data,"<="&B5) which is explained in more detail here . If the data contains all text values, or all numeric values, the rank will be correct. However, if the data includes both text and numbers, we need to “shift” the rank of all text values to account for the numeric values. This is done with the second part of the formula here:...
A stacked column chart is a basic Excel chart type to allow part-to-whole comparisons over time, or across categories. In a stacked column chart, data series are stacked one on top of the other in vertical columns. Stacked column charts can show change over time because it’s easy to compare total column lengths. However, except for the first series of data (next to the x-axis) and total bar length, it’s difficult to compare the relative size of the components that make up each bar....
About This Shortcut This shortcut toggles underlining on and off for the current selection. On Windows, Ctrl 4 also toggles underlining. About This Shortcut This shortcut toggles strikethrough formatting on and off for the current selection. Strikethrough formatting is one of four basic formatting options for text, which also includes bold, italic, and underline formatting. You can see what each option looks like in the samples below, which also include the shortcut to apply the formatting:...
Explanation In this example, the goal is create a VLOOKUP formula that will return an empty cell when the lookup result is an empty cell. When VLOOKUP can’t find a value in a lookup table, it returns the #N/A error. You can use the IFNA function or IFERROR function to trap this error. However, when the result in a lookup table is an empty cell , no error is thrown, VLOOKUP simply returns a zero....
