Explanation The REPLACE function will replace text by position. You can use REPLACE to remove text by providing an empty string ("") for the “new_text” argument. In this case, we want to remove the labels that appear inside text. The labels vary in length, and include words like “Make”, “Model”, “Fuel economy”, and so on. Each label is followed by a colon and a space. We can use the colon as a “marker” to figure out where the label ends....

Explanation The goal is to generate a series of “custom” days of the week based on a start date entered in cell B5. For example, you might want to list sequential dates for any of the following combinations of days: Mondays, Wednesdays, and Fridays (as shown) Tuesdays, Thursdays, and Saturdays Tuesdays and Thursdays Mondays and Fridays The number of dates to create (n) is entered in cell B8. If the start date or the number of dates to create is changed, the dates should be recalculated....

A slicer is a control that lets a user filter a Pivot Table or an Excel Table using buttons. A slicer provides the same function as using filter controls to filter a list. However, instead of drop-down menus, slicers provide large, friendly buttons that are always visible. The buttons are created automatically based on values in the data. In the example shown, the slicer is set up to filter on the Department field in the data....

Explanation In this example, the goal is to sum the quantities in column C by the codes in column E in a case-sensitive manner. The SUMIF function and the SUMIFS function are both good options for counting text values, and both functions support wildcards . However, neither function is case-sensitive, so they can’t be used to solve this problem. A good solution is to use the EXACT function with the SUMPRODUCT function , as explained below....

Purpose Return value Syntax =TEXT(value,format_text) value - The number to convert. format_text - The number format to use. Using the TEXT function The TEXT function in Excel is a tool for formatting numbers, dates, and times as text. The purpose of the TEXT function is to convert a number to text using a specified format code. TEXT is most often used to control the formatting of a number embedded into a text string....

About This Shortcut With extend selection enabled, you don’t need to press Shift to extend the current selection. About This Shortcut The ‘Go To’ window allows you to go to a named range or an address that you supply. It also has ‘Special’ button that will allow you to select cells based on several criteria - blank cells, cells that contain constants, cells with formulas, etc. Special is where the real power is....

Transcript What is Excel What is Excel? Excel is a spreadsheet program with a full spectrum of capabilities. Using Excel, you can run calculations, make lists and charts, analyze and organize information, track financial data, and much more. Let’s take a look. Every Excel workbook is a collection of worksheets. All the work you do in Excel will be done in a worksheet. Worksheets appear as tabs at the bottom of the Excel window, and the workbook name appears in the window title at the top....

Explanation Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use Boolean logic to create a lookup array on the fly composed only of 1s and 0s, then look for the value 1. This is the approach used in this example: =XLOOKUP(1,boolean_array,result_array) In this example, the required criteria is:...

Explanation In this example, we have a simple worksheet that uses the XLOOKUP function to lookup the name of a U.S. state when a valid two-letter code is provided as a lookup value. The goal is to remove the #N/A error that XLOOKUP returns when it can’t find a result. Although you could use the IFNA or IFERROR function to trap the error and provide an alternative result, the easiest solution is to add a value for the not_found argument , as explained below....

Purpose Return value Syntax =YEAR(date) date - A valid Excel date. Using the YEAR function The YEAR function returns the year of a date as a 4-digit number. YEAR takes just one argument, date , which must be a valid date. Date can be a cell containing a date, a date string, or a formula that returns a date. Internally, Excel converts dates to serial numbers following its own date system....

Explanation In geometry, the area enclosed by a trapezoid is defined by this formula: where a is the length of side a, b is the length of side b, and h is the height, measured at a right angle to the base. In Excel, the same formula can be represented like this: =(a+b)/2*h So, for example, to calculate the area of a trapezoid where a is 3, b is 5, and h is 2, you can use a formula like this:...

Explanation In this example, the goal is to average the Prices in C5:C16 when the Group in D5:D16 is not blank (i.e. not empty). The traditional way to solve this problem is to use the AVERAGEIFS function . However, you can also use the FILTER function with the AVERAGE function , as explained below. Because FILTER can work with ranges and arrays , it is a more flexible solution. Background study How to use the AVERAGEIFS function FILTER function basic example AVERAGEIFS Function The AVERAGEIFS function calculates the average of cells in a range that meet one or more conditions, referred to as criteria ....

This article describes one way to model Adwords profitability in Excel. If you want to try out the spreadsheet, it’s attached below. The most satisfying and powerful thing you can do with Excel is to model a real-world problem in a way that helps you make a better decision. For a model to be effective, it needs to expose inputs in a way that makes sense, and generate results that are easy to interpret....

Explanation For each cell in the range, SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number 1 is added to the final result, since the number of words is the number of spaces + 1. We’re using TRIM to remove any extra spaces between words, or at the beginning or end of the text....

Explanation This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. They are an alternative to using an Excel Table , which also resizes as data is added or removed. The INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns in a range....

About This Shortcut This shortcut will enter an array formula . When you use this shortcut to enter an array formula, you will see that curly braces surround the formula like so {=formula}. About This Shortcut This shortcut will calculate all worksheets in all open workbooks.

Shortcuts to undo, redo and repeat

About This Shortcut Movement is to the right through worksheets and will stop at the last worksheet to the right. To move to the last tab/worksheet in a workbook, hold down the control key and click the right navigation arrow in the lower left corner of the workbook. Mac Excel As of November 2025, there seems to be a conflict with Microsoft’s published shortcuts Control + PageDn (Next worksheet) and Control + PageUp (Previous worksheet)....

Explanation The QUARTILE function is automatic, and will calculate the 1st quartile with an input of 1, the 2nd quartile with an input of 2, and the 3rd quartile with an input of 3. With an input of 0, quartile returns the minimum value in the data. The trick in this case is to arrange the conditional formatting rules so that they run in the same direction. The first rule highlights values greater than the 3rd quartile....

Transcript In this video, we’ll look at how to quickly add and remove elements to a chart. In Excel’s lingo, chart elements include things like the chart title, the legend, gridlines, data labels, axis labels, and so forth. Before Excel 2013, chart elements were added using controls on the Layout tab of the ribbon, visible when a chart is selected. Starting with Excel 2013, the layout tab was removed and replaced by the chart elements control, which appears as a plus symbol when a chart is selected....