Posts

A stacked column chart can show part-to-whole comparisons over time, or across categories. In a stacked column chart, data series are displayed as vertical columns, stacked one on top of the other. In this worksheet, we have quarterly sales in 4 regions: East, West, North, and South. To start off, I’ll place the cursor in any cell, click the column icon next to Recommended Charts, and select Stacked column. The result is a column chart, where each column is built from quarterly sales in each region....

Structured references behave differently from other references when copied. Let’s look at some examples. First, notice if I copy and paste a formula that contains structured references, column names won’t change. For example, if I add a new column to the end of this table, then copy and paste the tax formula into the new column, Excel brings in the same formula without changes. This is true even if I copy and paste outside the table....

Wouldn’t it be great if there was a function that could count colored cells in Excel? Sadly, there isn’t any inbuilt function to do this. BUT.. It can easily be done. How to Count Colored Cells in Excel In this tutorial, I will show you three ways to count colored cells in Excel (with and without VBA): #1 Count Colored Cells Using Filter and SUBTOTAL To count colored cells in Excel, you need to use the following two steps:...

If you’ve ever had a long list of inputs/outputs in your workbook and wanted to give them defined names based on their labels, then this is a tips you’ll definitely want to learn as it’s a lot quicker than naming them individually using the Name Box or Name Manager. Since spaces aren’t allowed in defined names, this method also replaces any space characters in your labels with underscores. Use the Create from Selection command....

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.

As with dates, the key to entering a time in Excel is to enter it in a format that Excel will recognize as a time. When checking for a time, Excel will look for hours, minutes, seconds, and the AM or PM designation. Let’s take a look. You can, of course, enter a time with all components. For example, 7:00 PM can be entered like this: 7:00:00 PM And 8:30 AM like this: 8:30:00 AM However, Excel can understand the date in progressively shorter formats....

If you work with a lot of numbers in Excel, it’s a good practice to highlight negative numbers in red. This makes it easier to read the data. There are various techniques you can use to highlight negative numbers in red in Excel: Using Conditional Formatting Using Inbuilt Number Formatting Using Custom Number Formatting Let’s explore each of these techniques in detail. Highlight Negative Numbers in Red – Using Conditional Formatting Excel Conditional formatting rules are applied to a cell based on the value it holds....

Excel has a lot of useful in-built charts and you can also combine and create some amazing combination charts as well. Excel charts are a great way to show your data visually and are often the most used ones when you have to present it to your manager/clients. While your charts may be in Excel, it’s not necessary that it’s the best way to show them to your clients/managers. Often, it would be required to show these charts in a PowerPoint presentation or in an MS Word document of PDFs....

Setting a custom table style as the default table style is a little tricky. You need to create an Excel template that contains the custom style, and then make sure Excel uses the template when it starts up. Let’s walk through the process. First, create a new blank workbook. As a first step, I recommend that you highlight cell A1 in yellow. After you set up Excel to use a custom template, this makes it easy to verify that Excel is indeed using the template....

You can easily sort a data set by using the sort icons in the ribbon or the sort dialog box. Then why do you need to know how to do this using VBA? Knowing how to sort data using VBA can be helpful when included as a part of your code. For example, suppose you get a data set daily/weekly that you need to format and sort in a specific order....

When to use Excel IS Function IS function can be used when you want check a specific value and get either TRUE or FALSE based on the outcome. What it Returns It returns TRUE if the condition is met and FALSE if it is not met. Syntax =ISBLANK(value) =ISERR(value) =ISERROR(value) =ISLOGICAL(value) =ISNA(value) =ISNONTEXT(value) =ISNUMBER(value) =ISREF(value) =ISTEXT(value) Input Arguments value – the value that you want to test. It could be a blank (empty cell), error, logical value, text, number, or reference value, or a name referring to any of these....

This article explains how join tables using VLOOKUP and a calculated column index. This is one way to use the same basic formula to retrieve data across more than one column. In the example shown, we are using VLOOKUP to pull Name and State into the invoice data table. The VLOOKUP formula used for both is identical: In this case, the first instance of the formula in column E returns 5, since column E is the 5th column in the worksheet....

In the example shown, the formula in L5 is: By nesting multiple VLOOKUPs inside the IFERROR function, the formula allows for sequential lookups. If the first VLOOKUP fails, IFERROR catches the error and runs another VLOOKUP. If the second VLOOKUP fails, IFERROR catches the error and runs another VLOOKUP, and so on. 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....

But named ranges are actually a pretty cool feature. They can make formulas a lot easier to create, read, and maintain. And as a bonus, they make formulas easier to reuse (more portable). In fact, I use named ranges all the time when testing and prototyping formulas. They help me get formulas working faster. I also use named ranges because I’m lazy, and don’t like typing in complex references :)...

Note: for simplicity, we are using decimal (base 10) values, but there is a binary standard as well. See below. This formula works because digital units have a “power of 10” relationship. At the core, this formula separates the number part of the size from the unit, then divides the number by the appropriate divisor to normalize to Gigabytes. The divisor is calculated as a power of 10, so the formula reduces to this: To get the number, the formula extracts all characters from the left up to but not including the units: To get “power”, the formula matches on the unit in a hard-coded array constant: Which returns the position of the unit in the array constant....

First, let’s see the usual way of doing it. Suppose, you have a data set as shown below and you want to insert blank cells in C2:C4, in such a way that the data in C2:C4 should shift one column to the right The usual way of doing this is to first select C2:C4, then right click and Select Insert. In the Insert dialogue box, select Shift Cells Right. While this isn’t exactly a very long way to do this, there is a better and quicker way to do it....

In the example shown, the formula in C6 is: If you need to perform more than one replacement at the same time, you’ll need to nest multiple SUBSTITUTE functions. See the “clean telephone numbers” example linked below. If you need to replace a character at a specific location, see the REPLACE function. 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....

In this worksheet, we have a set of data without formatting. To illustrate how undo and redo work, I’ll make some changes to the worksheet. But first, notice that I’ve set up the Quick Access Toolbar to show the Undo, Redo, and Repeat commands. This will make it easier to see and understand the history of changes. First, I’ll add a formula to calculate total price… Next, I’ll apply currency formatting to the Unit Price and Total columns....

Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. This looks pretty complicated but the gist is that we create an array of all characters in B5, and test each character to see if it’s a number. If so, we discard the value and replace it with an empty string (""). If not, we add the non-numeric character to a “processed” array....

where data is the named range C5:H16. The result is 303, the sum of values in the range F5:H16. Note: The TAKE function is new in Excel. See below for options that will work in older versions. TAKE function The TAKE function returns a subset of a given array or range. The size of the array returned is determined by separate rows and columns arguments. When positive numbers are provided for rows or columns, TAKE returns values from the start or top of the array....