Posts

Transcript In this lesson we’ll look at how to apply basic horizontal alignment to cells—these are the options to left-align, right-align, and center-align content horizontally in cells. Let’s take a look. There are two basic paths to horizontal alignment in Excel. The first is the Alignment group on the home tab of the ribbon, which gives you access to left, right, and center alignment. The second path is the Alignment tab on the Format Cells dialog box....

Transcript When filtering a pivot table, you might want to filter the same field by Label and Value. By default, a pivot table won’t allow multiple filters on the same field. However, you can change a setting to enable this option when you need to. Let’s take a look. Here we have a pivot table that shows Sales and Orders by product, sorted by Sales. Let’s filter the products to show only the top 3 products by Sales....

Transcript In this video, we’ll look at how to create a list of random times. In this worksheet, let’s generate 20 random times between 7:00 AM and 6:00 PM. To do this, we’ll use the RANDARRAY function . Now, RANDARRAY can generate both integers and decimal values. For Times, we want decimal values, because Excel times are fractional values of one day. For example, 6:00 AM is .25, 12:00 PM is ....

Transcript How to save a workbook In this lesson we’ll look at several ways to save a document in Excel: The Quick Access toolbar The keyboard shortcut Control-S The Save button on the File tab Let’s take a look. After you’ve made changes to an Excel workbook you need to save the file to save your changes. You should get in the habit of saving your work on a regular basis so that you don’t lose work in the event of a system problem....

Transcript In this lesson, we’ll look at how to select entire rows and columns. Selecting columns and rows is handy when you want to move information around, delete information, or when you want to copy a row or column. Let’s take a look. To select a column in Excel, just click the letter in the column heading. You’ll see Excel immediately select the entire column. If you want to select more than one column, and the columns are together, just click a column letter and drag to expand your selection....

Do you find yourself creating new workbooks in Excel, then making the same changes to every one? Maybe you like to change font size, zoom percent, or the default row height? If so, you can save yourself time and trouble by setting a default template for Excel to use each time you create a new workbook. As long as you name the template correctly, and put it in the correct location, Excel will use your custom template to create all new workbooks....

Transcript In this video, we’ll look at a few ways to trap errors. Trapping errors can make your spreadsheets more professional by making them less cluttered and more friendly to use. In this first worksheet, we have a simple table that shows test scores for 5 sections. The total questions in each section are in column C, and the total questions that were correct are in column D. Column E contains a formula that calculates the percentage score in each section by dividing the correct answers by the total number of questions in the section....

Transcript Another way to duplicate or move data in Excel is to use drag and drop. Drag and drop is an elegant alternative to using copy and paste. It can save you a few steps, but it requires a little more dexterity. Let’s take a look. To move some information in Excel using drag and drop, first select the cells you’d like to move. Next, hover over the edge of the selection until you see the cursor change to a symbol with four arrows....

Transcript In this video, we’ll look at how to use named ranges with Excel tables. One problem with tables is that you can’t use structured references directly to create data validation or conditional formatting rules. However, as a workaround, you can define a named a named range that points to a table, and then use the named range for data validation or conditional formatting. To illustrate, here I have a list of projects representing a sales pipeline....

Explanation Note: in more recent versions of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER function. By default, lookup formulas in Excel like VLOOKUP and INDEX + MATCH will find the first match , but not other matches that may exist in a set of data. However, with some effort, you can make INDEX and MATCH return all matches....

Transcript In this video, we’ll introduce the idea of boolean values in Excel. A boolean is a data type with only two possible values. In Excel, these are the logical values TRUE and FALSE. You’ll notice that Excel treats TRUE and FALSE in a special way. If I type the word “true” in lowercase, Excel automatically capitalizes it. The same thing happens if I type “false”. Note also that Boolean values are automatically centered in the cell....

Purpose Return value Syntax =LEFT(text,[num_chars]) text - The text from which to extract characters. num_chars - [optional] The number of characters to extract, starting on the left. Default = 1. Using the LEFT function The LEFT function extracts a given number of characters from the left side of a supplied text string. The first argument, text , is the text string to extract from. This is typically a reference to a cell that contains text....

Transcript In this video, we’ll quickly review new Dynamic Array functions in Excel. With the introduction of dynamic array formulas, Excel includes 6 brand new functions that directly leverage dynamic array behavior. Each of these functions is covered in more detail later in the course, so this is just a brief demonstration. UNIQUE The UNIQUE function is designed to extract unique values from a set of data. This range contains a list of colors, some of which appear more than once....

Explanation The SUMPRODUCT function multiplies arrays together and returns the sum of products. The trick is to use simple array expressions to “cancel out” the irrelevant rates in the table. SUMPRODUCT then simply sums the rates that remain. Working from the inside out, this formula uses boolean logic to “filter” the rate data. The filter is constructed with three expressions, provided as the first argument to SUMPRODUCT: (dates>=J5)* // date greater than or equal to Dec 3 (dates<J6)* // date less than Dec 7 (rooms=J4)* // room is "King" Each of these expressions returns an array of TRUE FALSE values:...

Purpose Return value Syntax =PERCENTILE.INC(array,k) array - Data values. k - Number representing kth percentile. Using the PERCENTILE.INC function The Excel PERCENTILE.INC function calculates the “kth percentile” for a set of data, where k is between 0 and 1, inclusive. A percentile is a value below which a given percentage of values in a data set fall. A percentile calculated with .4 as k means 40% percent of values are less than or equal to the calculated result, a percentile calculated with k = ....

When a filter is applied to a Pivot Table, you may see rows or columns disappear. This is because pivot tables, by default, display only items that contain data. In the example shown, a filter has been applied to exclude the East region. Normally the Blue column would disappear, because there are no entries for Blue in the North or West regions. However, Blue remains visible because field settings for color have been set to “show items with no data”, as explained below....

Pivot tables have a built-in feature to allow manual grouping. In the example shown, a pivot table is used to group colors into two groups: Original and New. Notice these groups do not appear anywhere in the source data. Fields The source data contains three fields: Date , Sales , and Color . A fourth field, Group is created by the grouping process: Before grouping, the original pivot table looks like this:...

To create a pivot table that shows the last 12 months of data (i.e. a rolling 12 months), you can add a helper column to the source data with a formula to flag records in the last 12 months, then use the helper column to filter the data in the pivot table. In the example shown, the current date is August 23, 2019, and the pivot table shows 12 months previous....

Explanation In this example if a task is marked “Done”, then it is considered complete. The goal is to calculate the percent complete for the project by showing the ratio of complete tasks to total tasks, expressed as a percentage. The formula in F6 is: =COUNTA(C5:C11)/COUNTA(B5:B11) At the core, this formula simply divides tasks complete by the total task count: =complete/total which is then formatted as a percentage . To count completed tasks, we count non-blank cells in the range C5:C11 with the COUNTA function :...

Explanation The SUBSTITUTE function can find and replace text in a cell, wherever it occurs. In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which effectively removes the character completely. How can you figure out which character(s) need to be removed, when they are invisible? To get the unique code number for the first character in a cell, you can use a formula based on the CODE and LEFT functions:...