Purpose Return value Syntax =VDB(cost,salvage,life,start,end,[factor],[no_switch]) cost - Initial cost of asset. salvage - Asset value at the end of the depreciation. life - Periods over which asset is depreciated. start - Start period. end - End period. factor - [optional] Rate at which the balance declines. If omitted, defaults to 2. no_switch - [optional] Don’t switch to straight line. Default is FALSE. Using the VDB function The VDB function returns the depreciation of an asset for a given period, using the double-declining balance method or another method specified by changing the factor argument....

Transcript The key to formulas in Excel is cell references. Cell references tell Excel where to look to find the values it needs to calculate the result of a formula. Cell references can refer to one cell, a range of cells, or even entire columns and rows. Let’s take a look. Every cell in an Excel worksheet has a unique address. The address of each cell is defined by its location on the grid....

Explanation In the example shown, cell G4 contains the lookup value, “Berlin”. XLOOKUP is configured to find this value in the table, and return the population. The formula in G5 is: =XLOOKUP(G4,B5:B18,D5:D18) // get population The lookup_value comes from cell G4 The lookup_array is the range B5:B18, which contains City names The return_array is D5:D18, which contains Population The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last) To return County instead of population, only the return array is changed....

A 100% stacked bar chart is an Excel chart type designed to show the relative percentage of multiple data series in stacked bars, where the total (cumulative) of each stacked bar always equals 100%. Like a pie chart, a 100% stacked bar chart shows a part-to-whole relationship. However, unlike a pie chart, a 100% stacked bar chart can show how proportions change over time, for example, product market share changes per year, as shown above....

Explanation An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is to have $100,000 at the end of 10 years, with an annual payment of $7,500 made at the end of each year. What interest rate is required? To solve for the interest rate, the RATE function is configured like this in cell C9: =RATE(C7,-C6,C4,C5) nper - from cell C7, 10. pmt - from cell -C6, -7500 pv - from cell C4, 0....

A bar chart is one of Excel’s primary chart types and a good choice for categorical data. Bar charts plot data using horizontal bars, so they are very easy to read because the human eye can easily compare bars. Also, because of the horizontal layout, bar charts have room to accommodate longer category names. Bar charts are also versatile. They can be used to plot both nominal data and ordinal data , and they can be used instead of a pie chart to plot data with a part-to-whole relationship....

Overview | Why Pivot? | Story | Examples This article is for those of you who don’t get pivot tables. Maybe you tried pivot tables once, and didn’t see what the big deal was, or maybe you got frustrated when a pivot table wouldn’t behave. They can be that way. I’m not going to mince words. If you use Excel on a regular basis, you need to know how to use pivot tables....

Explanation In this example, the goal is to test a value in a cell to see if it contains a specific substring . Excel contains two functions designed to check the occurrence of one text string inside another: the SEARCH function and the FIND function. The difference is that the SEARCH function supports wildcards but is not case-sensitive, while the FIND function is case-sensitive but does not support wildcards. Both functions return the position of the substring in the text as a number when it is found and a #VALUE!...

Explanation In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Minutes Value Time 1 1/24 60 0.04167 1:00 3 3/24 180 0.125 3:00 6 6/24 360 0.25 6:00 4 4/24 240 0.167 4:00 8 8/24 480 0.333 8:00 12 12/24 720 0.5 12:00 18 18/24 1080 0.75 18:00 21 21/24 1260 0....

Explanation In this example, the goal is to get a count of people that hold shares in whole numbers. For example, Bob holds 100 shares (even), so he should be included in the whole number count, while Cindy holds 50.5 shares, so she should not be included in the whole number count. The first problem is how to determine whole numbers. This can be done with the INT , TRUNC , or MOD functions as explained in detail here ....

About This Shortcut The default behavior for drag and drop is to cut data in cells, but you can also copy cells during drag and drop. To copy cells, use this shortcut before or after you start to drag the selection. Excel will display a small plus (+) symbol to indicate the copy operation. About This Shortcut By default, when you drag a selection to a new location, the values in the selection will overwrite cells at the new location....

To make a dynamic chart that automatically skips empty values, you can use dynamic named ranges created with formulas. When a new value is added, the chart automatically expands to include the value. If a value is deleted, the chart automatically removes the label. In the chart shown, data is plotted in one series. Values come from a named range called “values”, defined with the formula provided below: =$C$4:INDEX($C$4:$C$30,COUNT($C$4:$C$30)) // values Axis labels come from a named range called “groups”, defined with this formula:...

Transcript In this video, we’ll take a look at the TEXTBEFORE function. Like the name suggests, TEXTBEFORE is designed to extract text that occurs before a specific marker, called a “delimiter”. A delimiter can be one or more characters. Let’s look at an example. In this worksheet, we have a list of email addresses. The goal is to extract the name portion of each email address into column D. As I start to enter TEXTBEFORE, Excel will match the function and I can use Tab to autocomplete....

About This Shortcut This shortcut extends the selection from the active cell to the cell immediately to the left . If multiple columns are selected to start with, Excel will shrink the selection by moving the entire right edge of the current selection one column to the left. About This Shortcut This shortcut extends the selection from the active cell to the cell immediately above. If cells in multiple rows are selected to start with, Excel will shrink the selection by moving the bottom row up....

Explanation A common situation in Excel is to use the SORT function to sort results returned by the FILTER function . However, a formula based on the FILTER and SORT may return an error when no data is returned. In this example, the goal is to create a formula based on FILTER and SORT that will not return an error when the FILTER function returns no data. Problem The formula below returns a #CALC!...

Exceljet courses are single-user access. However, you can purchase as many additional user logins as you need, either now or later. This bundle includes 10 additional user logins for an affordable price of $25 per user. When you buy additional user logins, we’ll ask you for a list of names and emails. Once we get the list from you, we’ll create a new account for each user. Each user will have permanent access to the same courses that appear in the original order....

Explanation The survey generated almost 3,000 responses, making it a perfect dataset to demonstrate Excel’s new capabilities. In this article, I’ll walk you through using the GROUPBY function to analyze these survey results step by step, showing you how this cool new function can quickly transform raw survey data into useful information. If you don’t have the new GROUPBY function , a regular Pivot Table would also be an excellent way to analyze these results....

Transcript In this video, we’ll look at how to use conditional formatting to shade every other row in a table. This is sometimes called “zebra striping”. In this spreadsheet, we have a table of employees with a small amount of formatting. To get shading on every other row, we could just convert this table to a proper Excel table using Control + T, then customize the format as desired. This is a good option, especially if you want the table for other reasons as well....

Transcript When you add a field as a row or column label in a pivot table, you automatically get the ability to filter the results in the table by items that appear in that field. Let’s take a look. This pivot table is displaying just one field: Total Sales. After we add Product as a row label, notice that a drop-down arrow appears in the header area. When we open this menu, we see a variety of filter options....

Transcript One of the most powerful features of pivot tables is their ability to group data, especially by number or date. In this video, I’ll show you how to group data by age range. Here we have a set of data that represents voting results. There are 300 results total, and, in each row, we have name, gender, age, and vote. I’ll go ahead and summarize the data in a pivot table....