Count birthdays by month
You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges, and won't let you use something like MONTH to extract just the month number from...Read more
You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges, and won't let you use something like MONTH to extract just the month number from...Read more
In this example, the goal is to sum by group, where each group is represented by a different color: Blue, Red, Green, and Purple. The worksheet shown contains two different approaches. In the range F5:G8, we have created a summary table to summarize counts by color. In column D, we are...Read more
In this example, the goal is to calculate a running total in column D of the worksheet as shown. A running total, or cumulative sum, is a set of partial sums that changes as more data is collected. Each calculation represents the sum of values up to that point. In this example, each calculation...Read more
The Excel GETPIVOTDATA function can retrieve specific data from a pivot table by name based on the structure, instead of cell references.Read more
The Excel VLOOKUP function is used to retrieve information from a table using a lookup value. The lookup values must appear in the first column of the table, and the information to retrieve is specified by column number. VLOOKUP supports approximate and exact matching...Read more
In this example, the goal is to count birthdays by year. The source data is an Excel Table named data in the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest have been previously ...Read more
In this example, the goal is to sum the amounts shown in column C by month using the dates in column B. The article below explains two approaches. One approach is based on the SUMIFS function, which can sum numeric values based on multiple...Read more
The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 rows.Read more
In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. The article below explains two approaches. One approach is based on the AVERAGEIFS function, which is designed to calculate...Read more
In this example, the goal is to group ages into buckets. One way to do this is to prepare a table with age breakpoints in the first column, and the name of the appropriate group or bucket in the second column. Then use a lookup function to find the right bucket or group for each age. In the...Read more
To do this, LOOKUP is configured as follows:
With this setup, LOOKUP performs an approximate match on the...Read more
In this example, the goal is to get the max value in the data for each month listed in column E. The easiest way to do this is with the MAXIFS function, which is designed to return a maximum value based on one or more criteria. In older versions of Excel without the MAXIFS function, you can use...Read more
If you need to group times into buckets (i.e. group by 6 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR.
In the example shown, we have a number of transactions, each with a timestamp. Let's say you want to group these transactions into buckets of 3...Read more
The Excel COLUMNS function returns the count of columns in a given reference. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns.Read more
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
This shortcut will delete columns that are selected.
Note: In Mac 2016, Control - also works (same as Windows).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
The Excel SUMIFS function returns the sum of cells that meet multiple conditions, referred to as criteria. To define criteria, SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be used with cells that contain dates, numbers, and text.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
Move to next / previous workbook window. This shortcut "cycles" through open workbooks each time it is used. To reverse direction, add the shift key.
On a Mac, check options at System Preferences > Keyboard > Shortcuts > Keyboard to confirm this shortcut is enabled.Read more
Use this shortcut to display the Paste Special dialog box. Paste Special is the gateway into many powerful operations, including paste Values, which you probably use every day. Note that this shortcut only works when data has been copied to the clipboard.
On Windows, once you have the...Read more
Paste will paste everything on the clipboard, both content and formatting. Use Paste Special to selectively paste only parts of what was copied. On the Mac, Ctrl + V also works.
You can also use Enter in Windows, and fn + Return on a Mac to paste from the clipboard.Read more
This shortcut will apply the Currency format with two decimal places. Excel offers many types of number formatting.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
This shortcut will create a new blank workbook.Read more
Copy 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 + C also worksRead more
This shortcut will select the entire column of the current selection. If the current selection spans multiple columns, all columns that intersect the current selection will be selected.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
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