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.
On Windows, Ctrl 4 also toggles underlining. 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, for an asset with an initial cost of $10,000, a useful life of 5 years, and a salvage value of $2,000, depreciation in year 1 can be calculated with SYD as follows: In the worksheet as shown, the formula in C7, copied down, is: Sum of years calculation In the example, the asset has a useful life of 5 years. Excel sums the years like this: This sum is then used in the calculation as follows: The asset loses 8000 in value over a 5 year period, so depreciation in period 1 = 5/15 * 8000 = $2,667, depreciation in period 2= 4/15 * 8000 = $2,133 , and so on....
Dear [boss], As you know, I spend a good part of my day in Excel. Although I can usually figure things out, I could be more productive with training. I think this might be a good option: https://exceljet.net/training The training is self-paced, with short videos I can fit into my schedule easily. The site is well organized, and they get good feedback. Would the company cover the cost as part of my skills development?...
Note: if you just need to calculate a “normal” quarter based on a quarter system that starts in January, you can use this simpler formula. In the example shown, the formula in cell D5 is: This formula returns a number, 1-4 that corresponds to a quarter system that begins in April and ends in March. In the case of fiscal quarters, we can use this same idea to map any incoming month (1-12) to one of 4 quarter values....
The result is the last value in column B. The data in B:B can contain empty cells (i.e. gaps) and does not need to be sorted. Note: This is an array formula. But because LOOKUP can handle the array operation natively, the formula does not need to be entered with Control + Shift + Enter, even in older versions of Excel. Working from the inside out, we use a logical expression to test for empty cells in column B: The range B:B is a full column reference to every cell in column B....
There are two primary ways to create a new table style: You can duplicate an existing style and customize, or 2. You can create a new style from scratch In this video, I’m going to create a simple minimal style from scratch. When creating a new style, you may want to turn off gridlines, to make it easier to see formatting. To create a new style, open the table styles menu and select New style from the bottom....
Let’s take a look. Before you clear conditional formats, you might want to verify where they are currently applied. Excel provides a convenient shortcut to select all cells with conditional formatting applied in the Find and Select menu, on the Home tab of the ribbon. Choose Find & Select, then Conditional Formatting. Excel will then select all cells that have conditional formatting applied in the current worksheet. You can then select Manage Rules from the Conditional Formatting menu and view the details of each rule....
While you can do this easily from the worksheet itself, sometimes you may want to use the VBA route to delete rows in Excel. These could be deleting a specific row, multiple rows in the selection, deleting alternate rows or those that have a specific value in it. In this tutorial, I will show you how to delete rows in Excel using VBA (multiple scenarios). So let’s get started!...
Let’s take a look. Here’s the table of names we looked at previously. Let’s add some formatting, then look at how to find this formatting. Let’s fill several cells that contain the name Ann with yellow. Let’s also bold two of these cells. Now let’s bring up the Find and Replace dialog box and search for the name Ann, with the option “Match entire cell contents” selected. As before, “Find Next” moves through all cells that contain Ann, one at a time, including the cells we just formatted....
To begin with, if you try and sort cells that have been merged, it will refuse to do so and show you an error that says – To do this, all the merged cells need to be of the same size. OHHH BUMMER! First of all, this warning does not specify that the issue is because of merged cells, but even if you know that there would be a huge task to unmerge these cells for sorting to work (especially if you have a huge data set)....
Let’s take a look. Here we have a simple coffee menu. Let’s improve the formatting by applying some custom fonts. To apply a font to one or more cells, first select the cells you’d like to format. In this case, let’s start by applying a new font to the entire worksheet. The easiest way to apply a font is to use the menu in the Font group on the home tab of the ribbon....
Let’s take a look. Here we have some raw data for subscriptions to an email newsletter. Each row shows the date that a user subscribed. Let’s quickly build a pivot table that summarizes new subscriptions by month. For convenience, I’ll first convert the data into a table. This will speed things up later, when we start working with the source data. Now I’ll summarize the table with a pivot table....
In this workbook, we have a number of Excel Tables. Let’s look at some ways you can remove these tables. You won’t find a “delete table” command in Excel. To completely remove an Excel table, and all associated data, you’ll want to delete all associated rows and columns. If a table sits alone on a worksheet, the fastest way is to delete the sheet. For example, this sheet contains a table showing the busiest airports in the world....
One of the common tasks many people have to do is to compare data in the same sheet. This is especially useful for people who work with large data that requires a lot of back and forth in the same worksheet. Excel has a split-screen feature that divides your window into horizontal or vertical panes, and then you can scroll these separately. This way, you can view row # 1 and Row # 500 in the same window (without scrolling back and forth) I will show you how the split-screen feature works in excel and why you should be using it....
When to use Excel MOD Function MOD function can be used when you want to get the remainder when one number is divided by another number. What it Returns It returns a numerical value that represents the remainder when one number is divided by another. Syntax =MOD(number, divisor) Input Arguments number – A numeric value for which you want to find the remainder. divisor – A number with which you want to divide the number argument....
One problem with tables is that you can’t use structured references directly to create data validation or conditional formatting rules. However, as a workaround, you can define named a named range that points to a table, and then use the named range for data validation or conditional formatting. To illustrate, here I have a list of projects representing a sales pipeline. The last column in the table uses data validation to provide a dropdown list of allowed values....
In the example shown, the formula in cell H6 is: {=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1))} where “files” is the named range C4:C11. Note: this is an array formula and must be entered with control + shift + enter. Context In this example, we have a number of file versions listed in a table with a date and user name. Note that file names are repeated, except for the code appended at the end to represent version (“CA”, “CB”, “CC”, “CD”, etc....
This is why almost all Excel experts recommend saving your work frequently (now Excel even has an autosave feature where it saves files automatically). While this is good practice, I recommend going even further and saving a new copy of your Excel file every day or every week (depending on how frequently you work on it and how important it is). And a fast way of saving a copy of your existing file is by using the ‘Save As’ option in Excel....
which sorts the text values in column B by string length, in descending order. In this example, we want to sort the values in B5:B15 by the number of characters each string contains. Working from inside out, we use the LEN function to get the length of each value: Because we give LEN an array with 11 values, we get an array with 11 lengths: Each number represents the character length of a value in B5:B11....
