Let’s take a look. Whenever you have a pivot chart selected, you’ll see a new ribbon called PivotChart Tools, that contains 4 tabs: Design, Layout, Format, and Analyze. These tabs give you a full set of controls to customize your pivot chart. Basic chart options are located on the Design Tab. You can change the chart type, alter the way the data is presented in the chart, control the layout used by the chart, apply a chart style, and move a chart to a new location....

This is the final chart. This project has a couple of interesting challenges. First let’s look at the available data. You can see we have data for both sunrise and sunset. Both columns contain valid Excel times. Now, if I try to create a column chart with just this data, we’ll have some problems. The clustered column option isn’t really useful. Stacked columns are closer to what we want. But, if you look closely, you can see that both sunrise and sunset are plotted as durations, instead of starting points, which isn’t going to work....

Let’s take a look. As you build more complicated workbooks, you might want to hide certain worksheets to keep things simple. Here we have a workbook that contains one worksheet for users to enter data and a reference worksheet that contains a lookup table. The easiest way to hide a worksheet is to right-click the worksheet tab and choose Hide from the menu. The worksheet will disappear, and the tab will no longer be visible at the bottom....

In this lesson, we’ll look at another conditional formatting option in the top and bottom category - the ability to highlight values that are above or below average. Let’s take a look. Here we have a table that contains three test scores for a group of students. Let’s use Conditional Formatting to quickly highlight values that are above or below average. There are two presets in the Top and bottom category under Conditional Formatting: Above average and Below average....

Let’s take a look. Slicer styles are available on the Slicer Tools ribbon. They control the color and style used to display slicers. As with pivot table styles, there are a wide variety of styles available, each with a distinctly different look. To apply a slicer style, select the slicer and then click one of the thumbnail images. Although you can create a new slicer style from scratch, usually it’s easier to start with an existing style that’s close to what you want....

In the example shown, we have a list of T-shirts that includes color and size. However, the size is abbreviated as “S” for small and “L” for large. There are only these two sizes in the data. Let’s say you want to write a formula to expand these abbreviations and show either the word “Small” or “Large” in column E. In other words: This is a perfect application of the IF function....

And the formula in G10 is: where client (B5:B17) date (C5:C17) and amount (D5:D17) are named ranges. Note: these are array formulas and must be entered with control + shift + enter, except in Excel 365. Note there is no actual rank in the source data. Instead, we are using the LARGE function to work directly with amounts. Another approach would be to add rank to the source data with the RANK function, and use the rank value to retrieve client names....

In the example shown, the formula in cell D6 is This tells Excel to take the value in B6 ($33.39 ) and round it to the nearest multiple of the value in C6 (5). The result is $35.00, since 35 is the next multiple of 5 after 33.39. In cell D10, we are rounding the same number, 33.39 up to the nearest multiple of 1 and get 34.00. You can use CEILING to round prices, times, instrument readings or any other numeric value....

To start off, if you first select an entire row or column, you can use a single shortcut to insert new rows or columns. You can select an entire row with shift and the spacebar. Then, to insert a row, use Control shift + in Windows, Control + I on a Mac. This shortcut is the same for inserting columns. To select an entire column, Use control + spacebar. Then insert with Control shift + in Windows, Control + I on a Mac....

When this formula is entered, the result is $136. This is the sum of numbers in the range F5:F16 where corresponding cells in C5:C15 are not equal to “Red”. Note that the SUMIFS function is not case-sensitive. SUMIFS solution In the example shown, the solution is based on the SUMIFS function. The formula in cell I5 is: In this formula, sum_range is F5:F16, criteria_range1 is C5:C16, and criteria1 is “<>red”....

If you continue to browse and use this website, you are agreeing to comply with and be bound by the following terms and conditions of use. If you disagree with any part of these terms and conditions, please do not use our website. Copyright This website contains material owned by us. This material includes, but is not limited to, articles, pages, videos, screenshots, PDFs, and images. You do not have permission to reproduce or distribute the content on this website in any form without our written consent....

Pivot charts are an extension of pivot tables. A pivot chart is always linked to a pivot table, and you can’t change one without changing the other. To illustrate this connection, I’ll start by creating a new pivot table and pivot chart. The first thing to notice is that both pivot tables and pivot charts change Excel’s interface when selected. When you select a pivot table, you’ll see the PivotTable Tools menu appear on the ribbon, with Analyze and Design tabs....

which outputs the 7 unique values seen in D5:D11. UNIQUE is a dynamic function. If any data in B5:B16 changes, the output from UNIQUE will update immediately. Dynamic source range UNIQUE won’t automatically adjust the source range if data is added or deleted. To feed UNIQUE a dynamic range that will automatically resize as needed, you can use an Excel Table, or create a dynamic named range with a formula....

Where “states” is the named range G5:H55. VLOOKUP is configured to get the lookup value from column C. The table array is the named range “states”, the column index is 2, to retrieve the abbreviation from the second column). The final argument, range_lookup, has been set to zero (FALSE) to force an exact match. VLOOKUP locates the matching entry in the “states” table, and returns the corresponding 2-letter abbreviation....

Let’s take a look. Here we have a simple table that summarizes sales by salesperson over a four-month period. What we’re going to do is use the INDIRECT function to make a dynamic reference to a named range that corresponds to each month. This is a useful technique when you want to provide an interactive way to change a reference on a worksheet. First, let’s set up a few formulas to get the basics working....

The first argument in the CHOOSEROWS function is array. Array can be a range, or an array from another formula. Additional arguments are in the form row_num1, row_num2, row_num3, etc. Each number represents a specific row to extract from the array, and should be supplied as a whole number. Basic usage To get rows 1 and 3 from an array, you can use CHOOSEROWS like this: To get the same two rows in reverse order: CHOOSEROWS will return a #VALUE!...

Examples The formulas below use the CONVERT function to convert yards to meters, Celsius to Fahrenheit, gallons to liters, and square meters to square yards: Measurement units The tables below show the units available to the CONVERT function in each category. In all cases, Unit can be used for either from_unit or to_unit in the same category. Note: the CONVERT function is case-sensitive. Metric prefixes The prefixes shown in the table below can be used with metric units by prepending the abbreviation to the unit....

In general, the DATE function is the safest way to create a date in an Excel formula, because year, month, and day values are numeric and unambiguous, in contrast to text representations of dates which can be misinterpreted. Note: to move an existing date forward or backward in time, see the EDATE and EOMONTH. Example #1 - hard-coded numbers For example, you can use the DATE function to create the dates January 1, 1999, and June 1, 2010 with the following syntax:...

The first argument, data_field, names a value field to query. The second argument, pivot_table, is a reference to any cell in an existing pivot table. Additional arguments are supplied in field/item pairs that act like filters to limit the data retrieved based on the structure of the pivot table. For example, you might supply the field “Region” with the item “East” to limit sales data to Sales in the East Region....

The ISTEXT function takes one argument, value, which can be a cell reference, a formula, or a hardcoded value. Typically, value is entered as a cell reference like A1. When value is text, the ISTEXT function will return TRUE. If value is any other value, ISTEXT will return FALSE. Examples The ISTEXT function returns TRUE if value is text: If cell A1 contains the number 100, ISTEXT returns FALSE: If a cell contains a formula, ISTEXT checks the result of the formula: Note: the ampersand (&) is the concatenation operator in Excel....