Transcript In this lesson, we’ll look at several ways to set column widths in Excel. You’ve probably noticed that columns in Excel do not expand to accommodate the data they contain. When the data in a cell is too wide to fit the column, it just overlaps the next column, or, it’s clipped when the column to the right contains data. You can fix this problem by setting the column width....

Transcript You might want to temporarily add conditional formatting to better understand data in a worksheet, or you might want to clear all rules and start fresh. In this lesson, we’ll look at how to clear or delete conditional formatting rules. Let’s take a look. Before you clear conditional formats, you might want to verify where they are currently applied. Excel provides a convenient shortcut to select all cells with conditional formatting applied in the Find and Select menu, on the Home tab of the ribbon....

Transcript In this video we’ll look at a few ways to generate random values with the RANDBETWEEN function. The RANDBETWEEN function is a simple function you can use to generate random numbers. For example, I can enter RANDBETWEEN with a bottom value of 1 and a top value of 100. When I press Enter, I get a random value between those two values. So, I can just drag the fill handle down to get 10 random numbers between 1 and 100....

Transcript In this lesson, we’ll look at how to insert and delete rows in Excel. It’s common to insert rows to make room for more information. Deleting rows is an easy way to remove information you no longer want or need. No matter how many rows you add or delete, the number of rows in the worksheet never changes. When you insert rows, rows are pushed off the worksheet at the bottom....

Transcript In this video, we’ll look at how to remove duplicates in an Excel Table. Once you have 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. You’ll find the Remove Duplicates command on the Data tab of the ribbon, in the Data Tools group....

Transcript In addition to sorting pivot tables by labels and by values, you can sort a pivot table manually, just by dragging items around. Let’s take a look. Here we have the same pivot table showing sales. Let’s add Product as a Row Label and Region as a Column Label. As you’ve seen previously, both fields are sorted in alphabetical order by default. If you don’t want to sort by label or by value, you can sort the table manually by dragging items around....

Transcript In this lesson we’ll look at the Percentage format. The Percentage format is made to display fractional values as percentages. For instance, the value .05, formatted as a percent, will display as 5%. Let’s take a look. In column B of our table we have a set of numbers in General format. Let’s first copy the numbers to the rest of our table. Now let’s apply the Percentage format. Although percentage is listed in the Number format menu on the ribbon, it’s faster to use the Percentage button just below the menu....

Explanation This formula uses two named ranges : things , and results . If you are porting this formula directly, be sure to use named ranges with the same names (defined based on your data). If you don’t want to use named ranges, use absolute references instead. The core of this formula is this snippet: ISNUMBER(SEARCH(things,B5) This is based on another formula ( explained in detail here ) that checks a cell for a single substring....

Explanation The goal is to do something if a cell contains one substring or another. Most users will think first of the IF function. However, one problem with IF is that it does not support wildcards like “?” and “*”. This means we can’t use IF by itself to test for a substring like “abc” or “xyz” that might appear anywhere in a cell . One option (seen in the example) is to create a logical test with ISNUMBER, SEARCH, and OR, then use the IF function to return a final result....

About This Shortcut If there is no note in the active cell, a new note will be created. If a note already exists in the active cell, this shortcut will open the note. About This Shortcut If there is no comment in the active cell, a new threaded comment will be created. If a comment already exists in the cell, the comment box will open.

Purpose Return value Syntax =LARGE(array,k) array - An array or range of numeric values. k - Position as an integer, where 1 corresponds to the largest value. Using the LARGE function The LARGE function returns a numeric value based on its position in a list when sorted by value. In other words, LARGE returns the “nth largest” value in the list where 1 corresponds to the largest value, 2 corresponds to the second largest value, etc....

Explanation In this example, the goal is to create a lookup formula with a variable sheet name. In other words, a formula that uses a sheet name typed into a cell to construct a reference to a range on that sheet. If the sheet name is changed, the reference should update automatically. The key to the solution explained below is the INDIRECT function which is designed to evaluate text as a worksheet reference....

Explanation In this example, the goal is to return the maximum value in a set of data while ignoring any errors that might exist. This problem can be solved with the AGGREGATE function or with the MAXIFS function, as explained below. MAX with errors The standard way to retrieve the maximum value in a range of data is the MAX function . However, if we try to use MAX like this:...

About This Shortcut This shortcut opens the Power Query Editor. It is only available in the latest version of Excel 365 . About This Shortcut Use this shortcut to duplicate objects on the worksheet, including charts, shapes, smart art, and text boxes. This is a handy way to maintain consistent formatting across a number of like objects. Note: as of February 2022, the Mac shortcut doesn’t seem to work in Excel 365....

In this example, a pivot table is used to show the year-over-year change in sales across 4 categories. Change can be displayed as the numeric difference (this example) or as a percentage. Fields The pivot table uses all three fields in the source data: Date, Sales, and Color: The Color field has been added as a Row field to group data by color. The Date field has been added as a Column field and grouped by year:...

Explanation The RANDBETWEEN function takes two numbers, a bottom and top number, and generates a random integer in between. Dates in Excel are serial numbers, so you can use the DATE function to create the lower number and the upper number. RANDBETWEEN then generates a number that falls between these two date values. Notes: The result of this formula must be formatted as a date to display correctly. The RANDBETWEEN function is volatile and will generate new numbers whenever a change occurs on the worksheet....

Purpose Return value Syntax =SECH(number) number - The hyperbolic angle. Using the SECH function The Excel SECH function returns the hyperbolic secant of a hyperbolic angle angle. Given 2 as input, the function returns 0.265802229 as output. =SECH(2) // returns 0.265802229 Explanation The hyperbolic secant is the reciprocal of the COSH function. =1/COSH(x) // equivalent to SECH(x) The plot below show’s the function’s output in Excel. Purpose Return value Syntax =SIN(number) number - The angle in radians for which you want the sine....

Purpose Return value Syntax =SEQUENCE(rows,[columns],[start],[step]) rows - Number of rows to return. columns - [optional] Number of columns to return. start - [optional] Starting value (defaults to 1). step - [optional] Increment between each value (defaults to 1). Using the SEQUENCE function The SEQUENCE function generates a list of sequential numbers in an array. The array can be one-dimensional, or two-dimensional, controlled by rows and columns arguments. SEQUENCE can be used on its own to create an array of sequential numbers that spill directly on the worksheet....

Explanation In this example, the goal is to calculate a weekly total using the data as shown. Notice each week corresponds to 5 rows of data (Monday-Friday) so we will need to sum values in every 5 rows. To build a range that corresponds to the correct 5 rows for each week, we use the OFFSET function. To sum the values returned by OFFSET, we use the SUM function. The complete formula in cell F4, copied down, is:...

Explanation The goal is to calculate a tax amount with both fixed and variable components according to the following logic: If the amount is less than $1000, only the base tax applies. If the amount is $1000 or greater, the result is the base tax + 15% * the amount over $1000 This problem can be easily solved with the IF function. The formula in C5 is: =IF(B5<limit,base,base+(B5-limit)*rate) Where rate (F4), base (F5), and limit (F6) are named ranges ....