About This Shortcut This shortcut will open Excel’s options area. We don’t know of a dedicated shortcut for this in Windows, so you’ll need to use Alt + F to open the File menu, then T to access the Options window. On the Mac, options are called “preferences”, and the shortcut command + , will open preferences in most applications, not just Excel. About This Shortcut This shortcut launches the Excel help system....

Pivot tables make it easy to quickly sum values in various ways. In the example shown, a pivot table is used to sum amounts by color. Fields The pivot table shown is based on two fields: Color and Amount . The Color field is configured as a row field, and the Amount field is a value field, as seen below: The Amount field is configured to Sum: You are free to rename “Sum of Name” as you like....

In this article, I want to show you how you can sometimes replace a more complicated IF formula with a more elegant MIN or MAX formula. 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....

If you’ve generated random numbers in Excel before, you’ll know there’s a limitation where, every time you make a change to the spreadsheet, functions like RAND , RANDBETWEEN , and RANDARRAY recalculate and return different values. In Excel lingo, functions with this behavior are called volatile functions . This volatility can be frustrating when you need random results to stay put. Fortunately, there’s a solution: a seeded random number generator. In this article, we’ll show you how to build one using Excel’s LAMBDA function, giving you full control over when your random numbers change....

Transcript Excel has many useful shortcuts for selecting cells. This includes shortcuts for selecting rows, columns, groups of cells, and even the entire worksheet. Let’s take a look. When you’re selecting individual cells, you can use the arrow keys to move the selection around. If you hold down the shift key and then press an arrow key, you can extend this selection in any direction without using the mouse. To select an entire column, press control + spacebar....

Transcript In this video, we’ll look at shortcuts for the operations Cut, Copy, and Paste, and related Drag and Drop commands. To copy cells to the clipboard, use the shortcut Control + C on Windows, and Command C on the Mac. To paste, use Control + V on Windows and Command + V on the Mac. Note that when formulas are copied, relative cell references will update automatically. Use cut when you want to actually move data or formulas from one location to another....

Purpose Return value Syntax =SUM(number1,[number2],[number3],...) number1 - The first value to sum. number2 - [optional] The second value to sum. number3 - [optional] The third value to sum. Using the SUM function The 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. The SUM function takes multiple arguments in the form number1 , number2 , number3 , etc....

Explanation This formula uses structured references to feed table ranges into the SUMIFS function. The sum range is provided as Table1[Total] , the criteria range is provided as Table1[Item] , and criteria comes from values in column I. The formula in I5 is: =SUMIFS(Table1[Total],Table1[Item],H5) which resolves to: =SUMIFS(F5:F89,D5:D89,"Shorts") The SUMIFS function returns 288, the sum values in the Total column where the value in the Item column is “Shorts”. Explanation At a high level, we use VLOOKUP to extract employee information in 4 columns with ID as the lookup value:...

Explanation In this example, we have a list of 100 issues in Columns B to D. Each issue has a date and priority. We are also using the named range dates for C5:C104 and priorities for D5:D105. Starting in column F, we have a summary table that shows a total count per month, followed by a total count per month per priority. We are using the COUNTIFS function to generate a count....

Purpose Return value Syntax =WEBSERVICE(url) url - The url of the web service to call. Using the WEBSERVICE function The WEBSERVICE function returns data from a web service hosted on the internet. The WEBSERVICE function is only available in Excel 2013 and later for Windows. A web service uses a protocol like HTTP to retrieve data in a machine-readable format like XML or JSON. For example, a formula that uses WEBSERVICE to call a fictitious web service hosted at somewebservice....

We ran a survey on Excel shortcuts. Here are the results, based on over 800 replies. I’ve also compiled a list of the most 20 popular Excel shortcuts below. This is a tricky business, because people often don’t know what to call shortcuts, or how to refer to them. So I’ve had to “interpret” many of the shortcuts people mentioned. That said, the patterns are clear, and the best shortcuts naturally bubble up again and again....

Formulas are the bread and butter of Excel. If you use Excel on a regular basis, I bet you use a lot of formulas. But crafting a working formula can take way too much time. In this article, I share some good tips to save you time when working with formulas in Excel. Video: 20 tips to save time with Excel formulas 1. Don’t add the final parentheses to a function Let’s start out with something really easy!...

Explanation In geometry, the area enclosed by a triangle is defined by this formula: where b represents the base of the triangle, and h represents the height, measured at right angles to the base. In Excel, the same formula can be represented like this: A=b*h/2 So, for example, to calculate the area of a triangle with a base of 4 and a height of 3: =4*3/2 // returns 6 In the example shown, the goal is to calculate the area for eleven triangles with base given in column B and height given in column C....

Explanation In the example shown, we have a 3-year bond with a face value of $1,000. The coupon rate is 7% so the bond will pay 7% of the $1,000 face value in interest every year, or $70. However, because interest is paid semiannually in two equal payments, there will be 6 coupon payments of $35 each. The $1,000 will be returned at maturity. Finally, the required rate of return (discount rate) is assumed to be 8%....

Explanation The MODE function has no built-in way to apply criteria. Given a range, it will return the most frequently occurring number in that range. To apply criteria, we use the IF function inside MODE to filter values in a range. In this example, the IF function filters values by group with an expression like this: IF(group=E5,data) This compares each value in the named range “group” against the value in E5, which is “A”....

Explanation In this example, the goal is to count the number of cells in a range that are over a certain number of characters in length, where the number ( n ) is provided as a variable in cell F4. This problem can be solved with the SUMPRODUCT and LEN functions like this: =SUMPRODUCT(N(LEN(B5:B15)>F4)) // returns 5 The formula returns 5 since there are five cells in B5:B15 that contain more than 40 characters....

Explanation In this example, the goal is to count the number of cells in a range that contain formulas. This problem can be solved with a formula based on the SUMPRODUCT and ISFORMULA functions, as explained below. Forecast values The values in the range C13:C16 are forecasts created with a formula based on the MROUND function . The formula in C13, copied down, is: =MROUND(C12*1.05,25) This formula generates values that are 5% higher than the previous month, rounded to the nearest multiple of 25....

Purpose Return value Syntax =COUNTIF(range,criteria) range - The range of cells to count. criteria - The criteria that controls which cells should be counted. Using the COUNTIF function The COUNTIF function counts cells in a range when they meet a specific condition. COUNTIF is one of Excel’s most widely used functions, and you will find it in all kinds of spreadsheets that calculate conditional counts based on dates, text, or numbers....

In Excel’s date system, dates are serial numbers beginning on January 1, 1900. January 1, 1900 is 1, January 2, 1900 is 2, and so on. More recent dates are much larger numbers. For example, January 1, 1999 is 36161, and January 1, 2010 is 40179. The table below shows a few examples of dates and their corresponding serial numbers: Date Number 1-Jan-1900 1 2-Jan-1900 2 3-Jan-1900 3 1-Jan-1999 36161 1-Jan-2010 40179 1-Jan-2020 43831 2-Jan-2020 43832 3-Jan-2020 43833 Because dates are just numbers, you can easily perform arithmetic on dates....

About This Shortcut This shortcut extends the selection from the active cell to the cell immediately below. If multiple rows are selected, Excel will extend the entire bottom edge of the current selection by one row. About This Shortcut 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....