If you work with an Excel file that needs to be updated or reviewed by other people, the option to track changes can come in handy. You can also use it when you’re updating a file and need to keep a track of all the changes you do yourself. This can be helpful when you revisit it sometime later. In this tutorial, I will show you how to track changes in Excel....
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.
I often use these when I am using formulas and I need to specify what a formula does and what a cell value means. If you’re collaborating with others, you can also add comments for other people’s reference. In this tutorial, I will show you all you need to know about comments – including how to insert comments in Excel, how to edit/delete comments, how to delete comments from the entire sheet or workbook, etc....
Step 1: Add a table style. Adding a table style is a quick and easy way to make your data stand out. Like magic, your table is perfectly styled and already much more appealing. Step 2: Add visualization to your data with in cell data bars. Now that the table is styled, let’s add some visualization. No one like looking at numbers when can see a picture instead. Repeat the above steps while this time only highlighting the Totals....
One common requirement is to multiply an entire column by a number (a constant value). In this tutorial, I will show you two easy ways to multiple an entire column with a given number. So let’s get to it! Mulitply Column with a Number Using Formula (Hardcoding the Value in Formula) Suppose I have a dataset as shown below, where I have the Sales Rep names in Column A and their current sales values in Column B....
Once you have data in a table, it’s easy to remove duplicates. Let’s go through some examples. In this first worksheet, I have a table with a list of US cities and states. Some of the entries are duplicates. You’ll find the Remove Duplicates command on the Data tab of the ribbon, in the Data Tools group. Remove Duplicates will display a list of column headers below, with buttons at the top to quickly uncheck and check all columns....
Sometimes you might be working on a formula that’s broken or unfinished and Excel won’t let you enter it as is. In this example, I’m working with an INDEX MATCH formula that has a problem. Each time I try to enter the formula, Excel complains. This isn’t such a big problem if you have plenty of time, but maybe you need to stop work and save the worksheet for another day....
And if you work with large datasets, selecting entire rows and columns is quite a common task. Just like with most things in Excel, there is more than one way to select a column or row in Excel. In this tutorial, I will show you how to select a column or row using a simple shortcut, as well as some other easy methods. I will also show you how to do this when you’re working with an Excel table or Pivot Table....
When you use a formula to apply conditional formatting, it can be tricky to set the formula up correctly. The dialog used to create and edit the formula doesn’t provide all the nice features that the formula bar does. You can’t easy check references, you don’t get function screen tips, and the window is too small. An easy workaround is to test the formula on the worksheet directly. For example, if we want to highlight odd numbers in this set of random values, we can just add the formula in an empty area, and point back at the values, starting with the first value....
The status bar at the bottom of the Excel window provides real-time data about currently selected cells and can give you a lot of good information without the need to write any formulas. Here we have test score data for a group of students. Let’s use the status bar to look at the information shown in column J. First note that the status bar can be customized to display different information....
Now you have two options: Either recreate it again or go through it manually one step at a time, OR Use the 2 formula debugging techniques I am about to share. Keep reading! Excel Formula Debugging Method #1 – F9 Key Here is how this works: For example, suppose you have the text TrumpExcel in Cell A1, and in cell A3 you have the following formula: Excel Formula Debugging Method #2 – Evaluate Formula Step by Step Other Excel tutorials you may find useful:...
where “names” is the named range C4:E7, and “groups” is the named range B4:B7. The formula returns the group that each name belongs to. Note: this is an array formula and must be entered with control shift enter. where names is the named range C4:E7. This generates a TRUE / FALSE result for every value in the data, and the double negative coerces the TRUE and FALSE values to 1 and 0 to yield an array like this: This array is 4 rows by 3 columns, matching the structure of “names”....
How to make this chart From this point, you can set the chart title and move the legend. 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 “code” is the named range B5:B12. Note: this is an array formula and must be entered with control + shift + enter. Here, each value in the named range “code” is evaluated with the logical test “first letter is not N”. The result is an array or TRUE and FALSE values like this: This array is fed into the MATCH function as the lookup array. The lookup value is TRUE, and match type is set to zero to force an exact match....
With a color of “red” and item of “hat” the result is $11.00 Note: This is an array formula and must be entered using Ctrl + Shift + entered This formula uses two nested IF functions, wrapped inside MAX to return the maximum price with two criteria. Starting with a logical test of the first IF statement, color = G6, the values in the named range “color” (B6:B14) are checked against the value in cell G6, “red”....
When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection. He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell. Something as shown below in the pic: There is no way you can do this with Excel in-built features....
One challenge that comes up frequently when sorting is a need to sort in a custom order. For example, in this worksheet, we have a list of opportunities, and we want to sort the list in the order that stages appear in column H. How can we do this with a formula? Well, to start off, if we try to sort the data with the SORTBY function, and provide the list of stages for the sort_by array, I’ll get an error....
The result is a horizontal array with nine characters that spills into the range D5:L5. Typically, the MID function is used to extract one or more characters from a text string. So, for example, you could use MID like this: The trick in this formula is to ask MID for all characters in the text string by providing an array for the start_num argument. For example, to ask for the three letters in “red”, we can use an array like this: To generate the array we need for num_chars we use the SEQUENCE function....
As the formula is copied across and down, it returns a sum of values associated with “A” and “B” in the table to the left. Working from the inside out, SUMPRODUCT is configured with two arguments, array1 and array2. The first array, array1, is set up to act as a “filter” to allow only values that meet criteria: Note: both references above are mixed references. $B5:$H5 has the columns locked, so that the formula can be copied across....
