A clustered column chart groups multiple date series by category in vertical columns. Clustered column charts can be a good way to show trends in each category, when the number of data series and categories is limited. In this worksheet, we have data that represents quarterly sales across 4 regions: East, West, North, and South. With data like this, we can easily build a clustered column chart that groups data by quarter or by region....
In this first sheet, we have a list of students with five test scores. I’ll use the LARGE function to extract the three best scores for each student. To get top values, use the LARGE function. The LARGE function needs an array and something called “k” which you can think of as “nth”. So, in this case, I’ll just point at the five scores for the array and enter “1” for k....
Let’s take a look. There are several different ways to insert symbols and special characters. One way, that’s not obvious, is to use AutoCorrect. AutoCorrect is a feature built into Excel that automatically substitutes one bit of text for another. You can find AutoCorrect settings on the File tab, under Options > Proofing > AutoCorrect options. Here, we can see that there are a few symbols already handled by AutoCorrect: copyright, registered, and trademark....
Let’s take a look. When you open a new workbook, or when you add new worksheets to an existing workbook, Excel uses a generic name for each sheet: Sheet1, Sheet2, Sheet3, and so on. As you build out a workbook to meet your needs, you’ll probably want to rename these sheets to keep things organized. The easiest way to rename a worksheet is to double-click its name. This will highlight the text of the name, and then you can type a new name....
In a previous tip, we showed you how to delete blank rows. In today’s ExcelJet tip, we’ll show you a cool way to delete rows that are missing values in one step; even when your list contains hundreds or thousands of rows. Let’s take a look. Here’s our big list. You can see that this is a list of users, and that one of the columns contains a Last login date....
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. We can, for example, move Peanut Butter Chocolate to the top of the list....
Let’s take a look. Let’s look first at the Fill option. Selecting a horizontal alignment of Fill will fill a cell with content, repeating the content as necessary. This is not very useful for ordinary content, but it can be used for other effects. For example, we could enter a hash symbol in a cell, choose the horizontal alignment Fill, and then copy the cell across several columns to create a border element....
Let’s take a look. The SUM function sums numeric values. For example, if I enter the formula =SUM(B7:B11) Excel will return “50,” the total of all numbers in that range. SUM simply ignores blank cells. The formula =SUM(D7:E9) will return “1,350”. Like blanks, SUM ignores text values. In addition to ranges, SUM can work with individual cells, too. Just separate each additional item with a comma. As you enter these values in, you’re free to combine both ranges and individual cell references....
On this worksheet, I have a table that contains employee data, named Table1. To illustrate how to work with VLOOKUP when the source data is in a table, I’ll set up formulas to the right to extract data from the table, matching on an employee ID. First, I’ll select the table header and use Paste Special with Transpose to get the field values. Then I’ll add some formatting, and an ID value so I have something to match against....
Let’s take a look. Here we have a set of random dates in column B. First, I’ll add a formula to column C to pick up the date values in B and format them with the General format, so we can see the raw value. You can see that these are normal date serial numbers, and B11 is the only cell that contains a time value. Using built-in functions, I’ll extract the year, month, and day from each date....
Table styles are a big part of formatting tables in Excel. In fact, Excel ships with over 50 built-in styles. Table styles apply a lot of formatting, but not all formatting. You can use table styles to apply border, fill, font formatting, and things like zebra striping. However, you can’t use table styles to apply number formatting, alignment, or conditional formatting. Every table has a style applied by default. The applied style will be highlighted in the table styles window....
The result is TRUE for any date in the last complete six month period, starting with the previous month. The TODAY function will continue to return the current date, so you can use a formula like this to create reports based on a rolling 6 months, rolling 12 months, etc. Excel dates are serial numbers, so you can manipulate them with simple math operations. The TODAY function returns the current date on an on-going basis....
Fields The pivot table shown is based on three fields: Age, Gender, and Rating. The Age field is added as a Row field, and the Gender field is added as a Column field. The Rating field is is added as a Value field: The Rating field in the Values area is renamed to “Avg Rating” and configured to Average: The custom name can be anything except an existing source field names (i....
This is a very simple tip that really demonstrates how you can leverage Excel’s formulas to create clever and compact solutions to everyday problems. To illustrate, let’s look at two examples. A free lunch with MAX Let’s say you have a $50 credit at a restaurant. It’s a one-time use credit that expires tomorrow, so you take your friend to dinner today. You split a salad, a pizza, and a couple of beers....
This returns only Saturdays or Sundays as the formula is copied down. The list is dynamic – when start date is changed, the new dates are generated. One of the the arguments provided to WORKDAY.INTL is called “weekend”, and indicates 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....
where data is an Excel Table in the range B5:E16, and the week numbers in column E are generated with the WEEKNUM function. WEEKNUM function The first step in this problem is to generate a week number for each date in column B. In the table shown, column E is a helper column with week numbers generated with the WEEKNUM function. The formula in E5, copied down, is: Note: because we are using an Excel Table to hold the data, we automatically get the structured reference seen above....
This formula sums the Price in column C when the Item name in column B contains an asterisk ("*"). Wildcards Excel functions like SUMIF and SUMIFS support the wildcard characters “?” (any one character) and “” (zero or more characters), which can be used in criteria. Wildcards allow you to create criteria to target cells that “begin with”, “end with”, “contain 3 characters” and so on. The table below shows some examples....
This worksheet contains several hundred rows of sample order data in an Excel Table called “data”. I’ll first build a pivot table to summarize this data by Color and Region. I’ll keep the pivot table on the same sheet. Then I’ll add Color as a row label, Region as a column label, and Total as a value field. I’ll also set Total to display currency. Finally, I’ll turn off Grand Totals, and disable Auto-Fit, to keep things from moving around....
In simple terms, an array formula is a formula that works with an array of values, rather than a single value. Array formulas can return a single result, or multiple results. That sounds simple enough, and indeed many array formulas are not complex. However, because some array formulas need to be entered in a special way, and some don’t, array formulas live mostly in the geeky realm of super users....
XLOOKUP returns $29.00, the price for a Medium Blue Hoodie. Note the lookup_value in XLOOKUP is 1, since the logical expressions in lookup_array create an array of 1s and 0s. Read below for details. Note: in older versions of Excel, you can use the same approach with INDEX and MATCH. Basic XLOOKUP The most basic use of XLOOKUP involves just three arguments: Lookup_value is the value you are looking for, lookup_array is the range you are looking in, and result_array contains the value you want to return....
