Here we have historical data showing average 30 year mortgage rates over a 5 year period. When I create a line chart, the vertical axis is a value axis showing the mortgage rate, and the horizontal axis is a category axis, grouping the data in specific date intervals. Let’s walk through some of the options for customizing the vertical value axis. To start off, right-click and select Format axis. Make sure you’re on the axis options icon....
Sometimes you get a set of data that isn’t complete because it’s been organized like an outline, with main headings and sub-headings appearing just once at the start of a new section. This isn’t a problem for humans because we can see and understand the overall structure of the data. But it won’t work if you want to analyze the data using filters, or pivot tables, or even conditional formatting....
Let’s take a look. When using the Find and Replace dialog box in Excel, there are actually two options for finding matches: Find Next, which we’ve already covered, and Find All. The Find All button will build a list of every cell that meets the current search criteria and report a total at the bottom. In this case, there are 19 cells that match the search for Ann. You can resize the window to show more files in the list....
So wouldn’t it be nice if there was a way to just jump to the bottom or jump to the top of that list? Well, there is. You just have to learn the keyboard shortcut. Let’s take a look. So here we are in Excel, and if I start scrolling, you can see this is a big list. In fact, this list is over 30,000 rows. So it’s just not practical for me to scroll to the top or the bottom of that list manually....
In this video we’ll show you a simple way to do it using the RAND function. Here’s a list of names that represent entries in a contest. Suppose we’d like to pick five winners. In a real drawing, we’d pick five random names out of a hat. But how do we do that in Excel? Well, when you use a hat, you can think of the winners as having the lowest numbers in the contest....
In this example, each student has five test scores in the same row, and the goal is to get the maximum score for each student. Data is supplied to MAX in a single range. The formula in I6 is: As the formula is copied down the table, MAX returns the top score for each student. MAX is fully automatic. If any scores are changed, MAX will automatically recalculate to show the latest....
In the example show, D5 contains this formula: Which returns the string “07”. Number format only The TEXT function converts numbers to text as a normal step in applying the number format. If you are concatenating (joining) the result to another text string, this is fine, but if you just want to apply visual padding to a free-standing number set of numbers, you can apply a custom number format without using a formula....
In the data, we have the fields: Name, Date, Amount, and Region. To start off, let’s look at total sales by region. This would be easy to graph in a Pivot Chart as well. Next, what are total sales by month and year? To answer this, we’ll need to group by Date. How about total sales by salesperson in 2014? The easiest way to build this report is to set Year as a Report filter....
Note: the source data contains data for an entire year, but the pivot table is filtered to show only the first 6 months of the year, January through June. Fields The source data contains three fields: Issue, Date, and Priority. All three fields are used to create the pivot table: The Date field has been added as a Row field and grouped by month: The Priority field has been added as a Column field....
In the example shown, the formula in E8 is: MATCH supports wildcard matching with an asterisk “” (one or more characters) or a question mark “?” (one character), but only when the third argument, match_type, is set to FALSE or zero. In the example, we pick up the value in cell E7 and use concatenation to combine this value with asterisks () on either side. The lookup array is the range B6 to B11, and match_type is set to zero to all partial matching with wildcards....
Here, total characters are calculated with the LEN function. From this number, the result of the following expression is subtracted: With number of characters not specified, RIGHT will return the last character in the string. If this characters is a forward slash “/”, the expression returns TRUE. If not, it returns FALSE. Because TRUE and FALSE are coerced automatically to 1 and zero in math operations, we subtract zero from the result of LEN when the last characters is not “/” and we subtract 1 when the last characters is “/”....
In the example, the formula in cell D7 is This tells Excel to take the value in B7 (PI) and round it to the number of digits in cell C7 (3) with a result of 3.142 Notice that even though the number in the 4th position to the right of the decimal is 1, it is still rounded up to 2. In the table, the ROUNDUP function is used to round the same number (pi, created with the PI function) to a decreasing number of digits, starting at 4 and moving down past zero to -1....
where data is the named range B5:B16. The result is the sum of the smallest 3 values (10, 15, 20) which is 45. SMALL function The SMALL function is designed to return the nth smallest value in a range. For example: Normally, SMALL returns just one value. However, if you supply an array constant (e.g. a constant in the form {1,2,3}) to SMALL as the second argument, k , SMALL will return an array of results instead of a single result....
Where Table1 is an Excel Table with the data range B105:F89. The sum range is provided as Table1[Total], the criteria range is provided as Table1[Item], and criteria comes from values in column I. The formula in I5 is: which resolves to: The SUMIFS function returns 288, the sum values in the Total column where the value in the Item column is “Shorts”. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
Example In the example, we are using this formula to dynamically lookup both rows and columns with VLOOKUP: H2 supplies the lookup value for the row, and H3 supplies the lookup value for the column. Note that the lookup array given to MATCH (B2:E2) representing column headers deliberately includes the empty cell B2. This is done so that the number returned by MATCH is in sync with the table used by VLOOKUP....
Let me take you through some of the material covered in the course. Core Excel begins with the basics. We cover the Excel interface, the concept of workbooks and worksheets, and many tricks for selecting cells and entering data. We also look at Excel’s powerful find and replace tools. Then we get you started with formulas and cell references. These are the key to setting up calculations in Excel, so we make sure you are properly introduced to important concepts like absolute and relative references....
which returns 25, the height in column B for model H in row 12. In the example shown, we are looking for the weight associated with Model H in row 12. The formula in H6 is: The lookup_value comes from cell H4 The lookup_array is the range E5:E14, which contains Model The return_array is B5:B14, which contains Weight The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last)...
The latest version of Excel ships with new functions like UNIQUE, SORT, FILTER and so on that make certain array formulas easy. But you can still build traditional array formulas as well, and they can solve some tricky problems. In this first example, we have high and low temperatures for seven days. We want to calculate the biggest change on any given day. This requires an array formula, because we don’t have a column in the data that calculates the change....
In the example, the formula in D6 is: This adds 7 days to Tuesday, Dec 22 and returns Tuesday, January 5, 2016. Subtract workdays To subtract business from a date (instead of adding workdays) just use a negative value for days. For example to get a date 3 workdays before a date in A1, you can use: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel. It’s important to understand however, that none of the alternatives will spill multiple results onto the worksheet into a spill range like a native dynamic array formula. This behavior is limited to the Office 365 version of Excel. Remember also that Pivot Tables can be used to solve many of these same challenges....
