Posts

With 3D referencing, you can refer to the same cell in multiple worksheets, and can also use this in formulas such as the SUM or AVERAGE, or COUNT. In this short tutorial, I will show you how to quickly sum across multiple worksheets using this 3D reference feature. Sum Across Multiple Sheets in Excel – Single Cell Below I have a dataset with quarter-wise sales for 10 stores. In the screenshot above, you can see the sales data for Quarter 1 in the sheet named Q1, and I have similar data for the other three quarters (Q2, Q3, and Q4) in three separate worksheets....

Let’s look first at the CODE function. The CODE function accepts one argument, which is the text for which you’d like a numeric code. If I use CODE with a capital A (“A”) it returns 65. I’ll get the same result if I supply a reference to B6, which also contains a capital “A.” With a lowercase “a” CODE returns 97. If I copy that formula down, you can see that CODE returns a number for each character, including punctuation and special symbols....

Let’s take a look. Here we have a large table that shows average monthly temperatures for Salt Lake City from 1949 through 2012. Let’s use a color scale to help visualize the numbers. There are twelve presets in the Color Scales category of the Conditional Formatting menu. The first six presets are 3-color color scales, and next six presets are 2-color color scales. For all presets, the color at the top is applied to the higher values in the selection, and the color at the bottom is applied to the lower values in the selection....

Being undocumented means that you will not find it in the formula list or as a part of the IntelliSense (the prompt that shows up when you type a formula name to show you the names of the matching functions). When to use Excel DATEDIF Function Excel DATEDIF function can be used when you want to calculate the number of years, months, or days between the two specified dates. A good example would be calculating the age....

When to use Excel LARGE Function Excel LARGE function can be used to get the Kth largest value from a range of cells or array. For example, you can get the 3rd largest value from a range of cells. What it Returns It returns the value which is the Kth largest value from a range of cells. Syntax =LARGE(array, k) Input Arguments array – the array or range of cells from which you want to fetch the kth largest value....

In the example shown, we want to mark rows where the color is red with an “x”. In other words, we want to test cells in column B, and take a specific action when they equal the word “red”. The formula in cell D6 is: In this formula, the logical test is this bit: This will return TRUE if the value in B6 is “red” and FALSE if not. Since we want to mark or flag red items, we only need to take action when the result of the test is TRUE....

The ReplaceChars function is designed to accept three input parameters: str is the text to perform replacements on, chars is a text string containing the characters to replace, and sub is is the character (or characters) to substitute when a character is found. In the example shown, ReplaceChars is configured to find the punctuation characters seen in the second argument (chars), and replace them with an empty string (""). This custom LAMBDA formula illustrates a feature called recursion, in which a function calls itself....

In the example shown, the formula in D5 is: Last relative position When constructing more advanced formulas, it’s often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. It’s important to understand that we are after the last relative position inside a given range not the row number on the worksheet:...

The INDIRECT function then coerces the text “Sheet1!A1” into a valid reference, which is passed into the SHEET function. The SHEET function then returns the current index for each sheet as listed. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

For example, if I’m using my VBA code to open a .exe file or any other format file (such as Word or PowerPoint), and I want a delay in my code so that I can see that the file has opened correctly. This can easily be done using the inbuilt SLEEP and WAIT commands in Excel VBA. Let’s look at a couple of examples you can use to delay or pause your VB code in Excel....

Where names is the named range B6:B17, and times is the named range D6:D17. Notes: this is an array formula and must be entered with Control + Shift + Enter. Later versions of Excel have a MAXIFS function, see note below. The result is an array of TRUE / FALSE values like this: TRUE values correspond to rows where the name is “Hannah”. For all other names, the value is FALSE....

With the POWER function The POWER function is another way to perform exponentiation in Excel. To get the nth root of a number with POWER, use the number with 1/n for the power argument: So for the example shown, the formula in D5 would be: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

Fields The source data contains two fields: Name, and Birthdate. Both fields are used to create the pivot table: The Birthdate field has been added as a Row field and grouped by Months only: The Name field has been added as a Value field. Because Name is a text field, the field is summarized by count automatically: Helper column alternative As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the month....

Fields The pivot table shown is based on three fields: First, Last, and Department. The Department field is configured as a Row field, and Last is configured as a Value field, renamed “Count”. The Last field is renamed “Count” and configured to summarize by count: In the example shown, the pivot table uses the Last field to generate a count. Any text field in the data that is guaranteed to have data can be used to calculate count....

The exercise is to verify the value of pi by plotting circumference vs. radius of a number of objects measured in a lab. Here is the data: Here is the original result: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

where amount (D5:D16), client (B5:B16), and date (C5:C16) are named ranges. Summary table setup The first step in solving this problem is creating the summary table seen in the range F4:I8. The values in F4:F9 are text values. However, the month names in G4:I5 are actually valid Excel dates, formatted with the custom number format “mmm”: SUMIFS solution The SUMIFS function can sum values in ranges based on multiple criteria....

Start is the named range G5, which is used by formulas in columns C and D, as explained below. The first step is to calculate the decimal hours as seen in column C. We could also work with Excel time, but decimal hours are more convenient when we calculate the day labels later below. The formula in C5, copied down, is: We subtract the start time from the end time and multiply by 24....

where code (B5:B15) and quantity (C5:C15) are named ranges. In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear anywhere in the lookup values in B5:B15. The formula in F5 is: lookup_value - E5, with asterisks (*) concatenated front and back lookup_array - the named range code (B5:B15) return_array - the named range quantity (C5:C15) if_not_found - the string “no match” match_mode - provided as 2 (wildcard match) search_mode - not provided....

The chart type is 100% stacked bar, and its a good example of how a bar charts can accommodate extremely long labels. The data used in the chart looks like this: How to make this chart Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

By embedding a PDF document inside an Excel workbook, you can keep all your important documents in one place. This can be especially helpful if you need to share the documents with other people. Portable Document Format files, or PDF’s, are incredibly popular document formats that are used by businesses and individuals all over the world. They are designed to be platform-independent, which means they can be opened on any type of device or operating system....