Posts

As the formula is copied down, it will return a “Happy Birthday!” when a birthday in column C matches the current date. Otherwise, the formula will return empty string (""), which displays as a blank cell. The long way The traditional “long way” solution to this problem is to use a formula like this: Working from the inside out, the logical test inside the IF function is based on the AND function: Inside the AND function, there are two logical expressions, one that checks the month, and one that checks the day: The MONTH function returns the month for a given date as a number (i....

Let’s take a look. Let’s create a pivot chart that shows product sales by Region. Since we have a pivot table already, we’ll create a pivot chart first, and then start customizing. Go to the Options tab of the PivotTable Tools ribbon and click the PivotChart button. For chart styles, we’ll use a simple column chart. At this point, our chart is plotting the full set of source data. Let’s filter the pivot chart to show only certain products: Banana Chocolate and Orange Chocolate....

Let’s take a look. This pivot table shows total sales by Product. Let’s add the Date field as a column label, and then group to show sales by year. When you first add a Date field as a row or column label, Excel will break out the data for every value in the field. To group a date field, first select one of the dates. Then right-click and choose Group from the menu....

Let’s take a look. Once a conditional formatting rule is defined, it can be revised or deleted. To see and edit a conditional formatting rule, select Manage Rules from the Conditional Formatting menu. By default the Manage Rules dialog box is set to the Current Selection. If you’ve already selected at least one cell that has the conditional formatting rule applied, the rule will appear automatically in the list. If not, switch the dropdown menu to This Worksheet....

Let’s take a look. When you add a label field to a pivot table, the items in that field are sorted alphabetically. For example, if we add Product as a row label to this pivot table. We get sales by Product, with all products listed alphabetically. If we remove the Product field and add the City field, we see sales by City, with cities in alphabetical order. This sorting is automatic for column labels as well....

When you apply conditional formatting with formulas, it can be hard to get the formulas to work properly, because you can’t see what happens to the formula when the rule is applied. You can think of conditional formatting as an “overlay” of invisible formulas that sit on top of the cells. When a formula in the overlay returns TRUE for a given cell, the formatting is applied. Formulas that don’t return TRUE (or the equivalent) do nothing....

Let’s take a look. Here we have a set of dates in column B of our table. Let’s start off by copying these dates to all columns. Let’s look first at the Short Date and Long Date formats. We can apply both of these date formats using the Number Format menu on the home tab of the ribbon. If we visit the Format Cells dialog box, we see that both the Short and the Long date formats are marked with an asterisk indicating that they are controlled by regional date and time settings in the operating system....

Let’s take a look. To start off, let’s copy the numbers in column B to the rest of our table. The format of all cells in the table is General, so let’s apply the Number format to columns C through H. The default settings for Number format are two decimal places, no commas to separate thousandths, and a standard minus sign for negative numbers. Let’s make adjustments to the Number format to match the headings in our table....

The results in column E are decimal values with the percentage number format applied. This same formula can be used to calculate things like percent of target, percent of budget, percent of forecast, and so on. Converting this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, the formula returns a decimal number for each city in the list. When these numbers are formatted with the Percentage number format, they are displayed as percentages....

And you can see we got Rank, Title, Director, Rating, Genre, and Runtime. So let’s use a Pivot table to answer some questions about this data, and my first question is: What are the top 10 films by rating? Okay, so there are the top 10 films in that list by rating. And the next question is more difficult, it’s: How do these films compare in terms of runtime…how many films are one hour, one-and-a-half hours, and so on?...

where “points” is the named range Rank returns a computed rank, which will include ties when the values being ranked include duplicates. Note the the RANK function by itself will assign the same rank to duplicate values, and skip the next rank value. You can see this in the Rank 1 column, rows 8 and 9 in the worksheet. The second part of the formula breaks the tie with COUNTIF: Note the range we give COUNTIF is an expanding reference: the first reference is absolute and the second is relative....

The formula returns only Tuesdays and Thursdays as the formula is copied down. If the start date in B5 is changed, new dates are dynamically generated. One of the the arguments provided to WORKDAY.INTL is called “weekend”, and controls which days are considered non-working days. The weekend argument can be provided as a number linked to a preconfigured list, or as a 7-character code that covers all seven days of the week, Monday through Saturday....

This setting controls the default for all new charts in all workbooks. Note that hen you just randomly select cell in a set of data, Excel will try to plot all data. If you only want to plot one series, you can hold down the control key and select just that data. Then Excel will only plot the data you’ve selected. Once you have a chart, there are a number of shortcuts you can use....

How this formula works I’m baby ullamco aliquip next level put a bird on it cronut salvia copper mug keffiyeh you probably haven’t heard of them deep v exercitation qui quinoa tilde brunch. VHS irony schlitz qui kickstarter pitchfork lyft green juice celiac asymmetrical velit. Man bun microdosing ea roof party 8-bit butcher franzen cillum vape. Eiusmod aute ad officia cronut. Prism sustainable ea, reprehenderit quis food truck irure YOLO try-hard ullamco paleo post-ironic tofu kogi....

COLUMNS counts the number of columns in any supplied range and returns a number as a result. For example, if we provide all of row 1 in a range, Excel returns 16,384 the total number of columns in an Excel worksheet. To count rows in a range, see the ROWS function. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel....

Above: Tracking public COVID-19 testing data in Excel with Power Query In this article, I want to share a quick example of how to track testing for COVID-19 using Excel and publicly available data. This is a bare bones tutorial, focused only on the basics of connecting Excel with publicly available data. The end result is a simple Excel table that shows the most recent testing data by state. The data is fetched and “shaped” with Power Query, then dropped back into Excel, where it can be refreshed with a single click....

which return the 6 unique rows in the range B5:C15, sorted by group. By default, UNIQUE will extract unique values in rows, so there is no special configuration needed. Working from the inside out, we give UNIQUE the full range of data, including the Group and Color columns: The UNIQUE function evaluates all 11 rows and returns the 6 unique rows in an unsorted two-dimensional array that looks like this: This array is returned directly to the SORT function: By default, SORT will sort based on the first column in the data....

In the example shown, the formula in E5 is: where “rates” is the named range G5:I8. The second lookup finds the offsite rate for same client and multiplies the result by the number of hours in column D: In the final step the two results are added together: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel....

A function is a special type of formula. You can think of a function as a pre-built formula with a specific purpose created to save time. Excel has hundreds of functions. To introduce the idea of a function, we’ll look at the SUM function. The SUM function is one of the most useful and widely used functions in Excel. Its purpose is to add things up. Let’s take a look....

where qty (B5:B13) and disc (C5:C13) are named ranges. The lookup_value comes from cell E5 The lookup_array is the named range qty (B5:B13) The return_array is the named range disc (C5:C13) The not_found argument is not provided The match_mode is set to -1 (exact match or next smaller) The search_mode is not provided and defaults to 1 (first to last) Note: Be aware if you supply an empty string ("") for not_found....