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.
When you first encounter SUMPRODUCT, it may seem boring, complex, and even pointless. But SUMPRODUCT is an amazingly versatile function with many uses. Because it will handle arrays gracefully, you can use it to process ranges of cells in clever, elegant ways. Worksheet shown In the worksheet shown above, SUMPRODUCT is used to calculate a conditional sum in three separate formulas: The results are visible in cells I5, I6, and I7....
The TEXT function takes two arguments, value and format_text. Value is the number to be formatted as text and should be a numeric value. If value is already text, no formatting is applied. Format_text is a text string that contains the number formatting codes to apply to value. Supply format_text as a text string enclosed in double quotes (""). To see examples of various number format codes, see Excel Custom Number Formats....
TEXTBEFORE takes six arguments; only the first two are required. The first argument, text, is the text string to process. The second argument, delimiter is the substring to use as a delimiter when extracting text. Both text and delimiter are required. The third argument, instance_num, is an integer that represents the nth instance of the delimiter in text (i.e. to extract the text before the second instance, use 2 for instance_num)....
With the Euro symbol in cell A1, the formula below has the same result: If text is more than one character, UNICODE returns the number of the first character: Note: the UNICHAR function performs the opposite conversion, returning the Unicode character at a given code point. Unicode Unicode is a computing standard for the unified encoding, representation, and handling of text in most of the world’s writing systems. Like ASCII, Unicode is a character set....
The XNPV function takes three arguments: rate, values, and dates. Rate represents the discount rate to apply to the cash flows. Enter rate as a percentage like 6% or the decimal value 0.06. Values represent a series of cash flows that correspond to dates. The first value is optional and corresponds to a cost at the beginning of the investment. If the first value is a cost or payment, it must be a entered as a negative number....
In the example shown, the formula in C5 is: The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text. This is a bit sloppy, but it avoids having to calculate the exact number of characters to extract. MID doesn’t care if this number is bigger than the remaining characters, it simply extracts all text following “@”. Next, we “flood” the remaining text with space characters, by replacing any single space with 100 spaces using a combination of SUBSTITUTE and REPT: This seems crazy, but the logic becomes clear below....
With January 1, 2000 in cell B5, the TEXT function returns the text string “Saturday”. For all examples, keep in mind that Excel dates are large serial numbers, displayed as dates with number formatting. Day name with custom number format To display a date using only the day name, you don’t need a formula; you can just use a custom number format. Select the date, and use the shortcut Control + 1 to open Format cells....
In the example shown, the VLOOKUP formula looks like this: In “exact match mode” VLOOKUP will check every value in the first column of the supplied table for the lookup value. If it finds an exact match, VLOOKUP will return a value from the same row, using the supplied column number. If no exact match is found, VLOOKUP will return the #N/A error. Note: it’s important to require an exact match using FALSE or 0 for the last argument, which is called “range_lookup”)....
where “holidays” is the named range G6:G8. The NETWORKDAYS function includes both the start and end date in the calculation, and excludes both Saturday and Sunday by default. The function will also exclude holidays when then are provided as the “holidays” argument as a range of valid dates. In these example shown, the first two formulas use the NETWORKDAYS function. If your workweek includes days other than Monday through Friday, you can switch to the NETWORKDAYS....
The SEARCH function returns the position of the value if found, and and the #VALUE error if not found. For B4, the results come back in an array like this: The ISNUMBER function changes all results to TRUE or FALSE: The double negative in front of ISNUMBER forces TRUE/FALSE to 1/0: The SUMPRODUCT function then adds up the results, which is tested against zero: Any non-zero result means at least one value was found, so the formula returns TRUE, triggering the rule....
If you want to highlight cells that end with certain text, you can use a simple formula based on the COUNTIF function. For example, if you want to highlight states in the range B4:G12 that end with “ota”, you can use: Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. The formula itself uses the COUNTIF function to “count” cells that end with “ota” using the pattern “ota” which uses a wildcard () to match any sequence of characters followed by “ota”....
Add your button to a sheet. To re-label the button to something more meaningful than button1, left click on the button and select Edit Text from the menu. You can also re-assign you button from this menu by selecting Assign Macro.
Let’s take a look. Here we have a table with several rows of sample text. The top three rows contain a single line of text. The bottom three rows contain larger amounts of text. The options available for vertical alignment in Excel are listed across the top of the table. Let’s format the text in each column to match the headings. For columns C, D, and E, it’s easiest to set vertical alignment using the three buttons in the Alignment group on the home tab of the ribbon....
In case you work with charts that are frequently updated, it’s better to create a dynamic chart range. What is a Dynamic Chart Range? A dynamic chart range is a data range that updates automatically when you change the data source. This dynamic range is then used as the source data in a chart. As the data changes, the dynamic range updates instantly which leads to an update in the chart....
A lot of times we have charts that have to handle empty data cells. Excel gives you the flexibility to decide how the empty cells should be treated. Suppose I have the data for date-wise sales of a product from a shop. Assuming that the shop is closed on Sunday and national holidays, there are some gaps on these days. Now if you plot this data in a chart in Excel, there are three options....
The Analysis Toolpak is an Excel add-in that provides a range of analysis features. It includes tools for regression analysis, correlation, covariance, and many other statistical calculations. The Analysis Toolpak can be a valuable asset in your data analysis arsenal. This post will show you how to install the add-in to your Excel. Install Analysis Toolpak Add-in The Analysis Toolpak add-in is available to all versions of Excel from 2007 onward....
But in many cases, you may want to move the chart from the sheet where it’s created to another worksheet (or a chart sheet – which has nothing but the chart). This can be useful when you’re creating dashboards, where the data can be scattered throughout the workbook, but you can get all the important charts in the sheet that has the dashboard. When it comes to moving a chart to another sheet, you can:...
Before you panic, and call IT to tell them Excel is broken, check to see if Scroll Lock is to blame. Read below to learn how. If you don’t understand what’s going on, this can be quite distressing :) Fortunately, the Scroll Lock setting is a toggle, much like Caps Lock. If you have a Scroll Lock key on your keyboard, just press it to toggle Scroll Lock off. Done....
Let’s take a look. This is a pretty basic table with a simple gray border. Let’s upgrade this design to something with a little more polish. First, let’s remove all borders. For this, we can use the preset in the border menu on the ribbon. Now let’s increase the row height to 30 for all the rows in the table. This will make it easier to use more color without the table feeling too crowded....
