Posts

Here we have some website data that includes orders and the conversion rates for five different channels. Let me first insert a column chart to plot orders per channel. Now, let’s say I’d like to also add the conversion rate into this chart. I can easily do that by selecting the chart and dragging the data area to include conversion. But now we see a problem. The conversion rate is measured as a percentage, where all numbers are less than 1, while the orders are much bigger numbers....

Let’s take a look. The General format has just two basic rules. The first rule is that if a number contains decimal places, they will be displayed up to the number that fits inside the column. When the column width is increased or decreased, Excel will adjust the number of visible decimal places automatically. If the width of a column is decreased, the number will be rounded as needed. The actual cell values remain unchanged....

The default row height in Excel is determined by the font size. As you increase or decrease the font size, Excel will adjust the row height to fit. However, you can still adjust the row height manually. Let’s take a look. All rows in Excel have a set height. To check on the current height of a row, just click the row divider at the bottom. You’ll see the height displayed in points....

A common example of a situation that requires concatenation is assembling a mailing address from data in separate columns. If I want to create a mailing address using this data, I need to create a formula that uses CONCATENATION to bring the name, street, city, state and zip together. In addition to cell references, I also need to include “literal text” for spaces and a comma. This works, but notice that everything just ends up on the same line....

Let’s take a look. The easiest way to add a new worksheet to a workbook is to click the Insert Worksheet tab that sits to the right of the last tab in the workbook. When you click this button, Excel will immediately add a new worksheet to the workbook to the right of the last worksheet. No matter which tab is selected, the new worksheet goes to the end of the list....

Here we have a simple model that shows the expense of making coffee at home vs. buying coffee in a coffee shop. Let’s take a look through the formulas in this model to see how they work. First, let’s find all the formulas. We can easily do that by using Go To Special and then selecting Formulas. When I click OK, all formulas in the worksheet are selected. To make it easier to see which cells contain formulas, I’ll go ahead and add a fill to these cells....

Let’s take a look. Like previous examples, we have a simple worksheet that highlights several cell references. Following the instructions in the table, let’s create the formulas we need to use exponents. The first formula doesn’t use any cell references. For 3 squared, we input = 3 ^ 2 which gives us 9. Excel doesn’t care about white space in formulas, so feel free to add space for better readability....

On this worksheet, I have two identical sets of order data. I’m going to walk through the process of constructing a summary of sales by item for both sets of data. With the data on the left, I’ll use standard formulas and no table. On the right, I’ll create an Excel Table, and build formulas that use structured references. To start off, I’ll add a bit of formatting to the data on the left to make it easier to see....

Table slicers have a variety of settings. To access these settings, select a slicer and click “Options” under the Slicer Tools menu in the ribbon. Options appear grouped in 5 main categories Name and settings 2. Slicer styles 3. Commands to arrange and align slicers 4. Controls for slicer buttons, and 5. Inputs to set slicer size The Slicer Tools ribbon will appear whenever you select a slicer. The slicer settings button opens a window with more settings....

where data (B5:E15) and n (G5) are named ranges. This formula returns TRUE if a row is a “last n row”, and FALSE if not. In brief, we get the current row in the workbook, then subtract the first row number of the range plus 1. The result is an adjusted row number that begins at 1. The INDEX function is simply one way get the first cell in a given range: This is for convenience only....

The workbook contains four worksheets: one summary, and three months, “Jan”, “Feb”, and Mar". Each of the month sheets has the same structure, which looks like this: The formulas on the summary tab lookup and extract data from the month tabs, by creating a dynamic reference to the sheet name for each month, where the names for each sheet are the month names in row 4. The VLOOKUP function is used to perform the lookup....

Since C7 has no value in the screen above, the formula shows no result. In the screen below, C7 contains a number and the sum is displayed: In the example shown, we are using the IF function together with the COUNT function. The criteria is an expression based on the COUNT function, which only counts numeric values: As long as the range contains three numbers (i.e. all 3 cells are not blank) the result is TRUE and IF will run the SUM function....

The result is a decimal value that is formatted using the percentage number format. However, since we don’t have a column for the number of students absent in the table, we need to calculate this number as part of the formula: After we convert this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, the formula returns calculated “percent absent” for each class listed in the table....

Pivot Table Fields In the pivot table shown, there are three fields, Name, Date, and Sales. Name is a Row field, Date is a Column field grouped by month, and Sales is a Value field with the Accounting number format applied. The Date field is grouped by Month, by right-clicking on a date value and selecting “Group”. Steps Notes Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

The asterisk (*) is a wildcard for one or more characters. By concatenating asterisks before and after the value in D5, the formula will count the value as a substring. In other words, it will count the value if it appears anywhere inside any cell in the range. Any positive result means the value was found. By comparing the result with the greater than operator (>) and zero, we force a final result of TRUE or FALSE....

The result is $88.00, the sum of the Total in F5:F16 when the Color in C5:C16 is “Red” and the State in D5:D16 is “TX”. Note that the SUMIFS function is not case-sensitive. SUMIFS function The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria. To apply criteria, the SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The syntax for the SUMIFS function depends on the number of conditions needed....

The problem is that the formula area in a conditional formatting rule isn’t very friendly. You don’t get highlighted cell references, you don’t get function autocomplete…heck….you don’t even get screen tips. As a result, it’s hard to “see” if a formula is going to work until after you save the rule. If it doesn’t, you have to use trial and error: This isn’t much fun, and it can be really frustrating when you run into a tricky problem....

In the example shown, the formula in F6 is: The lookup value comes from cell E6, which must be a valid date. The table array is the range B6:C11, and the column index is 2, since the amounts are in the second column of the table. Finally, zero is provided for the final argument to force an exact match. The VLOOKUP function locates the date value for Sept 4, and returns the value at the same row in the second column: 12,500....

In the example shown, the formula in D5 is: Note: make sure results are formatted as time. As a result, if you have a decimal value for 15 minutes, and a time in A1, you can add 15 minutes of time to the value in A1 like this: With the TIME function You can also add time values with the TIME function. To add 15 minutes to a time in A1, use: The TIME function saves you from having to remember the formula for converting decimal minutes to an Excel time....

In this worksheet, we have a list of names, scores, and groups. Currently the data is not sorted. Our goal is to sort this data by group, then by score in descending order. I’ll start off by placing the cursor in cell F5, then typing an equals sign (=) and the first few letters of “sortby”. Once we have a match, I’ll press the TAB key to complete. The first argument is called array....