Looking for more? Try chatting with the Exceljet Chatbot.
The SUMIFS function is designed to sum numeric values using multiple criteria. In the example shown, the data in the range B5:E15 shows a sales pipeline where each row is an opportunity owned by a salesperson, at a specific stage. The formula in H5 is:...Read more
The Excel TRIMRANGE function removes empty rows and columns from the outer edges of a range of data. The result is a "trimmed" range that only includes data from the used portion of the range. Because it is a formula, TRIMRANGE will update the range dynamically when data is added or removed...Read more
Dynamic Excel offers 6 brand new functions that solve hard problems in Excel like sorting, filtering, and working with unique values. For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel.Read more
Hold down the shift key and click elsewhere on the worksheet to extend the current selection to include adjacent cells.Read more
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.Read more
The Excel SUBTOTAL function is designed to run a given calculation on a range of cells while ignoring cells that should not be included. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see complete list below), and SUBTOTAL function can either include or exclude values in hidden...Read more
Cut puts everything on the clipboard: text, formulas, formatting, borders, fills, etc. Use Paste Special to selectively paste only parts of what was copied.
On the Mac, Ctrl + X also worksRead more
If the active cell is empty, Excel will stop on the first non-empty cell to the right. If the active cell is non-empty, Excel will stop on the last non-empty cell to the right.
On a Mac, the control key (⌃) can be used instead of the command key (⌘).Read more
If the active cell is empty, Excel will extend the selection to the first non-empty cell to the right. If the active cell is non-empty, Excel will extend the selection on the last non-empty cell to the right. On a Mac, the command key (⌘) can be used instead of the control key.Read more
If the active cell is empty, Excel will extend the selection to the first non-empty cell below the active cell. If the active cell is non-empty, Excel will extend the selection on the last non-empty cell below the active cell. On a Mac, the command key (⌘) can be used instead of the control key...Read more
The first time you use this shortcut, Excel will select the current region around the active cell, if one can be detected, otherwise the whole sheet is selected. The second time you use the shortcut (consecutively), Excel will select the entire worksheet.Read more
The delete key on a Mac deletes to the left.Read more
While editing a formula, this shortcut toggles cell references from relative to absolute, to partially absolute, back to relative again:
A1 --> $A$1 --> A$1-- > $A1-- > A1
This is much faster and easier than typing the $ character manually.
To convert an existing...Read more
The default behavior for drag and drop is to cut data in cells, but you can also copy cells during drag and drop. To copy cells, use this shortcut before or after you start to drag the selection. Excel will display a small plus (+) symbol to indicate the copy operation.Read more
The Excel REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value.Read more
If freeze pane is enabled, this shortcut will go to the upper left cell in the current pane. If freeze pane is not enabled, this shortcut will go the cell upper left cell in the worksheet (A1).Read more
This shortcut will delete columns that are selected.
Note: In Mac 2016, Control - also works (same as Windows).Read more
Note: there are many ways to summarize data with COUNTIFS, SUMIFS, etc. This example shows one specific and arbitrary way. Before you go the formula route, consider a pivot table first, since pivot tables are far simpler to set up and do most of the hard...Read more
A picture is worth a thousand words. Nothing beats an effective chart for presenting data in a way people can instantly understand. Excel has a powerful and deep charting engine built right in, so you already have the tools you need to create a wide range of charts and visualizations. This...Read more
This article provides examples of public Coronavirus data you can download to Excel with Power Query. Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook....Read more
The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. You can use the WEEKDAY function inside other formulas to check the day of week.Read more
The table in B3:D11 is a log that shows courses completed by various people. If a course has been completed by a person, there will be an entry in the table with name, course, and date. For the purpose of this example, if we find and entry for a given name/course, we can assume that course is...Read more
In this example, the goal is to calculate a total quantity for each color across the two ranges shown in the worksheet. The two ranges are "non-contiguous", which means they are not connected or touching. Both ranges contain a list of colors in the first column and quantities in the second...Read more
This shortcut clears or resets the filter for a selected slicer. You must select the slicer first before using the shortcut.
Note: if you want to reset all filters applied to an Excel Table at the same time, you can use a shortcut to toggle filters...Read more
Excel contains over 500 functions, with more functions added every year. That is a huge number, so where should you start? This guide provides a walkthrough of over 100 important functions in Excel with many examples and links. Click function names for details and more examples....Read more
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...Read more
The Excel TRANSPOSE function "flips" the orientation of a given range or array: TRANSPOSE flips a vertical range to a horizontal range, and flips a horizontal range to a vertical range.Read more
If you need to group times into buckets that are not the same size (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.) you can use the VLOOKUP function in approximate match mode.
There are several ways to group times in Excel. If you just need to group times by the hour, a pivot...Read more
The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the formula.Read more
