Move to first cell in worksheet
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
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
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
Moves current selection one cell below the active selection.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 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
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
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
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
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
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
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
This shortcut will allow multiple levels of undo; each time you use it Excel will step back one level.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 PIVOTBY function is designed to summarize data by grouping rows and columns. The result is a dynamic summary table created with a single formula.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 COUNTIFS function returns the count of cells in a range that meet one or more conditions. Each condition is provided with a separate range and criteria, and all conditions must be TRUE for a cell to be included in the count. COUNTIF can be used to count cells that...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
Hold down the control key and click (or click and drag) elsewhere on the worksheet to add another range of calls to the current selection.Read more
The Excel GROUPBY function is designed to summarize data by grouping rows and aggregating values. The result is a summary table created with a single formula.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
The Excel SORT function sorts the contents of a range or array in ascending or descending order. Values can be sorted by one or more columns. SORT returns a dynamic array of results.Read more
This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is:
type,G$4
where type is the named range D5:D14...Read more
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.Read more
In this example, the goal is to get all names in a given group into the same row, in separate columns, as seen in the worksheet. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns using common values, which in this case are the...Read more
In this example, the goal is to get all names in a given group into separate rows grouped by column, as seen in the worksheet above. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns where each column holds the names that belong to a...Read more
Number formats are a key feature in Excel. Their key benefit is that they change how numeric values look without actually changing any data. Excel ships with a huge number of different number formats, and you can easily define your own. This guide explains how custom number formats work in...Read more