Meet fellow New Skills Academy students in our study group. Join now!

Microsoft Excel Advanced Certificate

100%
Module 21 : Depreciation Formula

Module 21 : Depreciation Formula

Duration: Approx 20 minutes

21.1 Depreciation Formula


Excel offers five different methods for calculating depreciation.

The type you would use depends on how you want to calculate depreciation and how an asset depreciates over its life.

The five functions are:

    1. SLN - The Straight Line function. This will show depreciation that is equal for each period over the set timescale.

    2. SYD - The Sum of Years' Digits function.

    3. DB - The Declining Balance function.

    4. DDB - The Double Declining Balance function.

    5. VDB - The Variable Declaring Balance function.

21.2 Video Instruction



Depreciation Formula in Excel – 2m 27s







21.3 Task


Estimated Time: 10 minutes


Download the worksheet that accompanies this video by clicking below. Once downloaded, follow the video above and you will see how the results are reached for the Depreciation function.


Download Worksheet

Download Worksheet - Without Formula


    << Previous Next >>

    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    Learning Topics


    1
    Module 1: Add a Password
    20 minutes
    2
    Module 2 : AND Function
    20 minutes
    3
    Module 3 : Auto Updating Drop-down Lists
    20 minutes
    4
    Module 4 : CEILING Formula
    20 minutes
    5
    Module 5 : Charts that Update Automatically
    20 minutes
    6
    Module 6 : CHOOSE Formula
    20 minutes
    7
    Module 7 : Compare 2 Lists with VLOOKUP Function
    20 minutes
    8
    Module 8 : CORREL Function
    20 minutes
    9
    Module 9 : Create an Advanced Calculator
    20 minutes
    10
    Module 10 : Create a Combo Chart
    20 minutes
    11
    Module 11 : Create a Custom Tab
    20 minutes
    12
    Module 12 : Create a Pivot Chart
    20 minutes
    13
    Module 13 : Create Visual In-cell Indicators
    20 minutes
    14
    Module 14 : Create Visual In-cell Indicators with Icon Sets
    20 minutes
    15
    Module 15: Customise the Status Bar
    20 minutes
    16
    Module 16: DATEDIF Function
    20 minutes
    17
    Module 17: DATEVALUE Function
    20 minutes
    18
    Module 18: DAVERAGE Function
    20 minutes
    19
    Module 19 : DCOUNT Function
    20 minutes
    20
    Module 20 : DCOUNTA Function
    20 minutes
    21
    Module 21 : Depreciation Formula
    20 minutes
    22
    Module 22 : DGET Function
    20 minutes
    23
    Module 23 : DMAX Function
    20 minutes
    24
    Module 24 : DPRODUCT
    20 minutes
    25
    Module 25: DSUM Function
    20 minutes
    26
    Module 26: EDATE Function
    20 minutes
    27
    Module 27: EOMONTH Function
    20 minutes
    28
    Module 28: Find and Replace
    20 minutes
    29
    Module 29: FIND Function
    20 minutes
    30
    Module 30: INDEX Function
    20 minutes
    31
    Module 31: INDEX MATCH Function
    50 minutes
    32
    Module 32: INDIRECT Function
    20 minutes
    33
    Module 33: Insert Serial Number Automatically
    20 minutes
    34
    Module 34: IPMT Function
    20 minutes
    35
    Module 35: ISBLANK Function
    20 minutes
    36
    Module 36: ISERR Function
    20 minutes
    37
    Module 37: ISLOGICAL Function
    20 minutes
    38
    Module 38: ISNONTEXT Function
    20 minutes
    39
    Module 39: ISNUMBER Function
    20 minutes
    40
    Module 40: ISODD Function
    20 minutes
    41
    Module 41: Isolate First Name
    20 minutes
    42
    Module 42: ISTEXT Formula
    20 minutes
    43
    Module 43: LEFT Function
    20 minutes
    44
    Module 44: LEN Function
    20 minutes
    45
    Module 45: MATCH Function
    20 minutes
    46
    Module 46: VLOOKUP Multiple Results at Once
    20 minutes
    47
    Module 47: OR Function
    20 minutes
    48
    Module 48: PMT Function
    20 minutes
    49
    Module 49: REPLACE Function
    20 minutes
    50
    Module 50: Select all Cells with Comments
    20 minutes
    51
    Module 51: Sparkline Charts in Excel
    20 minutes
    52
    Module 52: Straight Line Depreciation
    20 minutes
    53
    Module 53: Strikethrough
    20 minutes
    54
    Module 54: Sum of Years Depreciation
    20 minutes
    55
    Module 55: Timeline in Pivot Table
    20 minutes
    56
    Module 56: VLOOKUP with 2 Criteria
    20 minutes
    57
    Module 57: VLOOKUP with DropDown List
    20 minutes
    58
    Module 58: VLOOKUP with MAX
    20 minutes
    59
    Module 59: VLOOKUP with MIN
    20 minutes
    60
    Module 60: Watch Window In Excel
    20 minutes

    Module 13 : Create Visual In-cell Indicators

    data formatting

    Visit Module

    Module 29: FIND Function

    29.1 FIND Function


    You can use the FIND function in Excel to get the location of specific text inside a string of text.

     also find multiple instances of a letter. FIND can be used in conjunction with other logical formulas such as

     IF, ELSE, OR, AND, VLOOKUP, HLOOKUP and INDIRECT to look up data.

    =FIND(find_text, within_text, [start_num})

     

    Visit Module

    Module 37: ISLOGICAL Function

    Check if value inc ell is logical. ISLOGICAL part of IS group returning TRUE/FALSE. TRUE if data is logical -FALSE if not. Used with cell address to provide a value. Example is B4 logical based. TO DO =ISLOGICAL(value)-- value, the value to check. 

    THIS CAN be used with IF to return specific data ( if the value is TEXT) 

    Visit Module

    Module 50: Select all Cells with Comments

    Comments are a useful feature, stores info in a cell. commetted cells have a lil red dot in the corner. Some worksheets have plenty of comments this finds them and allows you the edit them. TO DO - 

    Visit Module

    Module 1: Add a Password

    pass word 

    Visit Module

    Module 26: EDATE Function

    26.1 EDATE Function


    The EDATE function in Excel allows you to return the serial number of a date that is the specified amount of months after a set date.

     

    You can then convert this into a date format.

     

    The formula is written as: =EDATE(start_date, months)

     

    Start_date - This is the start date that you would like to calculate the months from.

     

    Months - The number of months you would like to calculate from the start date.

     

    It is worth noting that the first date that can be used with this formula is January 1, 1900, as Excel does not use serial numbers for anything prior to this period. Any date before January 1, 1900 will only return an error.
    Visit Module

    Module 53: Strikethrough

    As with other Microsoft Office programs and similar software or apps for data management and word processing, it is possible to Strikethrough text in Excel.
     
    You can choose single cells, multiple cells, or even entire rows or columns. This is useful to note for a variety of reasons, such as when you may wish to disregard a comment within a document but not delete it entirely.
     
    To use strikethrough, you can add it to your Quick Access Toolbar for easy access:-
     
    Click the arrow next to the Quick Access Toolbar, choose More Commands
     
    Click the drop-down to choose All Commands
     
    Then scroll down to Strikethrough, select it and click Add, then OK.
     
    You can then select the text you wish to put a line through and choose the Strikethrough button in the Quick Access Toolbar.
    Visit Module

    Module 2 : AND Function

    So the cell is greater than 80, and cell > 80, and cell > 80 will either make the cell say true or false. 

    =AND(D6>80,AND(E6>80),AND(F6>80) 

     

    OR WITH IF

     

    Visit Module

    Module 3 : Auto Updating Drop-down Lists

    Make the date you want a list from a table so you can add new infomation. Go to Data then date validation 

    Visit Module

    Module 5 : Charts that Update Automatically

    Charts that Update Automatically

    To make a chart update automatically you can select your existing list and format it as a table by going to the Home tab and clicking on Format as Table. Now you can add to the bottom of your source list and the data will automatically appear in your chart.

    GOOGLE SHEET

    Use Dynamic Ranges: If you expect new data to be added regularly, consider using functions like OFFSET or INDIRECT to define a dynamic data range. This way, the range will automatically adjust as you add new data.

    • Example: =OFFSET(A1,0,0,COUNTA(A:A),COUNTA(1:1)) – This formula will create a dynamic range starting from cell A1, expanding downward and rightward automatically as new data is added.
    Visit Module

    Module 6 : CHOOSE Formula

    the first argument is where the cell is going to be placed in and the following arguments are values starting from the same first cell . The following arguments are where you want the results to be . this is a look up function like v and h

    Visit Module

    Module 7 : Compare 2 Lists with VLOOKUP Function

    Comparing two lists in Excel using the VLOOKUP formula is a common task that can help you find matches or differences between these lists. Here’s a step-by-step guide on how to do it:

    Step 1: Prepare Your Lists

    1. Arrange Lists: Ensure your two lists are in separate columns. For example, List 1 is in Column A, and List 2 is in Column B.

    Step 2: Use the VLOOKUP Formula

    1. Formula Placement: Click on the cell where you want the comparison results to appear. This could be next to your first list (e.g., in Column C, next to List 1).

    2. Enter VLOOKUP Formula: The basic syntax of the VLOOKUP formula is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

      • lookup_value: The value in List 1 you want to find in List 2. For example, if you're starting with the first item in List 1, this would be A2.
      • table_array: The range that contains List 2. If your List 2 is in B1 to B100, then the range is B$1:B$100. The dollar signs ($) fix the range so that it doesn’t shift when you copy the formula down.
      • col_index_num: Since you’re looking up in a single column (List 2) and you want to return the matching value from this column, this number is 1.
      • [range_lookup]: Use FALSE to find an exact match.

    3. Complete the Formula: For instance, if you are comparing the value in cell A2 with values in column B (from B1 to B100), the formula would be =VLOOKUP(A2, B$1:B$100, 1, FALSE).

    Step 3: Drag the Formula Down

    1. Copy Formula: After entering the formula in the first cell (e.g., C2), drag the fill handle (a small square at the bottom right of the cell) down to copy the formula to the other cells.

    Step 4: Analyze the Results

    1. Interpreting Results: The VLOOKUP formula will return the matching value from List 2. If there’s no match, it will return an error (typically #N/A).
    2. Highlight Differences: Use Conditional Formatting to highlight cells in your results column that contain errors, indicating items in List 1 that are not in List 2.

    Tips:

    • Data Consistency: Ensure that the data in both lists are consistent (e.g., no extra spaces, same case) to avoid missed matches.
    • Error Handling: You can wrap your VLOOKUP formula in an IFERROR function to handle errors more gracefully. For example: =IFERROR(VLOOKUP(A2, B$1:B$100, 1, FALSE), "Not Found"). This will display "Not Found" instead of an error if there’s no match.
    • Duplicate Values: VLOOKUP will return the first match it finds. If List 2 contains duplicates, it won’t necessarily find all instances.

    By using the VLOOKUP formula, you can effectively compare two lists in Excel to identify matches or differences, which is especially useful in various data analysis tasks.

    Visit Module

    Module 8 : CORREL Function

     

    Correl Formula

     

     

     

     

     

     

     

     

     

     

    Month

    Avg Temp

    Air Cond.

    Sales

     

    Advertising

     Costs

    Sales

    Jan

    20

    100

     

    £2,000 

    £20,000 

    Feb

    30

    200

     

    £1,000 

    £30,000 

    Mar

    30

    300

     

    £5,000 

    £20,000 

    Apr

    40

    200

     

    £1,000 

    £40,000 

    May

    50

    400

     

    £8,000 

    £40,000 

    Jun

    50

    400

     

    £1,000 

    £20,000 

     

     

     

     

     

     

     

    Correlation

    86%

     

    Correlation

    28%

     

     

     

     

     

     

     

     

     

     

     

     

    This function examines two sets of data to determine the degree of relationship between the two sets

     

     

     

     

     

    The result will be a decimal between 0 and 1.

     

     

     

     

     

    The larger the result, the greater the correlation.

     

     

     

     

     

     

     

     

     

     

     

    In Table 1, the Monthly temperature is compared against the Sales of air conditioning units.

     

     

     

     

     

    The correlation shows that there is an 0.864 relationship between the data.

     

     

     
    Visit Module

    Module 52: Straight Line Depreciation

    Microsoft Office Course says 100% complete but I am on Section 52.

    Visit Module

    Frequently Asked Questions

    To progress on to the next module of your course scroll to the bottom of the current module and press the “NEXT” button. This will take you directly to the following module.

    Once you have completed the last module of the course please click on the button at the bottom of the module that says “START”. This will take you to the test.

    The pass mark for the end of course test is 70%

    Yes, you can re-take your test as many times as you need to.

    No, once you have passed a test it is not possible to retake it.

    When logging on to your course directly through our site you have lifetime access.

    Click on the “My Profile” button at the top of any page on our site. This will take you to your profile page. Once there scroll down to the section headed “Edit Your Profile” and enter your new password.

    Please click here to contact our student support team

    If you would like to go back and view a completed module you can do this by clicking the “Course Modules” option from the side bar menu of your course. This will take you to a list of the modules contained within your course. Simply select the module you wish to study again.

    Please click here to find out how to save your course modules as PDF files

    Worksheets

    Module 1: Add a Password

    Module 1: Add a Password

    Module 2 : AND Function

    Module 2 : AND Function

    Module 3 : Auto Updating Drop-down Lists

    Module 3 : Auto Updating Drop-down Lists

    Module 4 : CEILING Formula

    Module 4 : CEILING Formula

    Module 5 : Charts that Update Automatically

    Module 5 : Charts that Update Automatically

    Module 6 : CHOOSE Formula

    Module 6 : CHOOSE Formula

    Module 7 : Compare 2 Lists with VLOOKUP Function

    Module 7 : Compare 2 Lists with VLOOKUP Function

    Module 8 : CORREL Function

    Module 9 : Create an Advanced Calculator

    Module 10 : Create a Combo Chart

    Module 10 : Create a Combo Chart

    Module 11 : Create a Custom Tab

    Module 12 : Create a Pivot Chart

    Module 12 : Create a Pivot Chart

    Module 13 : Create Visual In-cell Indicators

    Module 13 : Create Visual In-cell Indicators

    Module 14 : Create Visual In-cell Indicators with Icon Sets

    Module 14 : Create Visual In-cell Indicators with Icon Sets

    Module 15: Customise the Status Bar

    Module 16: DATEDIF Function

    Module 16: DATEDIF Function

    Module 17: DATEVALUE Function

    Module 17: DATEVALUE Function

    Module 18: DAVERAGE Function

    Module 18: DAVERAGE Function

    Module 19 : DCOUNT Function

    Module 19 : DCOUNT Function

    Module 20 : DCOUNTA Function

    Module 20 : DCOUNTA Function

    Module 21 : Depreciation Formula

    Module 21 : Depreciation Formula

    Module 22 : DGET Function

    Module 22 : DGET Function

    Module 23 : DMAX Function

    Module 23 : DMAX Function

    Module 24 : DPRODUCT

    Module 24 : DPRODUCT

    Module 25: DSUM Function

    Module 25: DSUM Function

    Module 26: EDATE Function

    Module 26: EDATE Function

    Module 27: EOMONTH Function

    Module 27: EOMONTH Function

    Module 28: Find and Replace

    Module 29: FIND Function

    Module 29: FIND Function

    Module 30: INDEX Function

    Module 30: INDEX Function

    Module 31: INDEX MATCH Function

    Module 31: INDEX MATCH Function

    Module 31: INDEX MATCH Function

    Module 31: INDEX MATCH Function

    Module 31: INDEX MATCH Function

    Module 31: INDEX MATCH Function

    Module 31: INDEX MATCH Function

    Module 31: INDEX MATCH Function

    Module 31: INDEX MATCH Function

    Module 31: INDEX MATCH Function

    Module 32: INDIRECT Function

    Module 32: INDIRECT Function

    Module 33: Insert Serial Number Automatically

    Module 33: Insert Serial Number Automatically

    Module 34: IPMT Function

    Module 34: IPMT Function

    Module 35: ISBLANK Function

    Module 35: ISBLANK Function

    Module 36: ISERR Function

    Module 36: ISERR Function

    Module 37: ISLOGICAL Function

    Module 37: ISLOGICAL Function

    Module 38: ISNONTEXT Function

    Module 38: ISNONTEXT Function

    Module 39: ISNUMBER Function

    Module 39: ISNUMBER Function

    Module 40: ISODD Function

    Module 40: ISODD Function

    Module 41: Isolate First Name

    Module 41: Isolate First Name

    Module 42: ISTEXT Formula

    Module 42: ISTEXT Formula

    Module 43: LEFT Function

    Module 43: LEFT Function

    Module 44: LEN Function

    Module 44: LEN Function

    Module 45: MATCH Function

    Module 45: MATCH Function

    Module 46: VLOOKUP Multiple Results at Once

    Module 46: VLOOKUP Multiple Results at Once

    Module 47: OR Function

    Module 47: OR Function

    Module 48: PMT Function

    Module 48: PMT Function

    Module 49: REPLACE Function

    Module 49: REPLACE Function

    Module 50: Select all Cells with Comments

    Module 51: Sparkline Charts in Excel

    Module 51: Sparkline Charts in Excel

    Module 52: Straight Line Depreciation

    Module 52: Straight Line Depreciation

    Module 53: Strikethrough

    Module 53: Strikethrough

    Module 54: Sum of Years Depreciation

    Module 54: Sum of Years Depreciation

    Module 55: Timeline in Pivot Table

    Module 56: VLOOKUP with 2 Criteria

    Module 56: VLOOKUP with 2 Criteria

    Module 57: VLOOKUP with DropDown List

    Module 57: VLOOKUP with DropDown List

    Module 58: VLOOKUP with MAX

    Module 58: VLOOKUP with MAX

    Module 59: VLOOKUP with MIN

    Module 59: VLOOKUP with MIN

    Module 60: Watch Window In Excel

    Module PDF's

    Module 1: Add a Password

    Module 2 : AND Function

    Module 3 : Auto Updating Drop-down Lists

    Module 4 : CEILING Formula

    Module 5 : Charts that Update Automatically

    Module 6 : CHOOSE Formula

    Module 7 : Compare 2 Lists with VLOOKUP Function

    Module 8 : CORREL Function

    Module 9 : Create an Advanced Calculator

    Module 10 : Create a Combo Chart

    Module 11 : Create a Custom Tab

    Module 12 : Create a Pivot Chart

    Module 13 : Create Visual In-cell Indicators

    Module 14 : Create Visual In-cell Indicators with Icon Sets

    Module 15: Customise the Status Bar

    Module 16: DATEDIF Function

    Module 17: DATEVALUE Function

    Module 18: DAVERAGE Function

    Module 19 : DCOUNT Function

    Module 20 : DCOUNTA Function

    Module 21 : Depreciation Formula

    Module 22 : DGET Function

    Module 23 : DMAX Function

    Module 24 : DPRODUCT

    Module 25: DSUM Function

    Module 26: EDATE Function

    Module 27: EOMONTH Function

    Module 28: Find and Replace

    Module 29: FIND Function

    Module 30: INDEX Function

    Module 31: INDEX MATCH Function

    Module 32: INDIRECT Function

    Module 33: Insert Serial Number Automatically

    Module 34: IPMT Function

    Module 35: ISBLANK Function

    Module 36: ISERR Function

    Module 37: ISLOGICAL Function

    Module 38: ISNONTEXT Function

    Module 39: ISNUMBER Function

    Module 40: ISODD Function

    Module 41: Isolate First Name

    Module 42: ISTEXT Formula

    Module 43: LEFT Function

    Module 44: LEN Function

    Module 45: MATCH Function

    Module 46: VLOOKUP Multiple Results at Once

    Module 47: OR Function

    Module 48: PMT Function

    Module 49: REPLACE Function

    Module 50: Select all Cells with Comments

    Module 51: Sparkline Charts in Excel

    Module 52: Straight Line Depreciation

    Module 53: Strikethrough

    Module 54: Sum of Years Depreciation

    Module 55: Timeline in Pivot Table

    Module 56: VLOOKUP with 2 Criteria

    Module 57: VLOOKUP with DropDown List

    Module 58: VLOOKUP with MAX

    Module 59: VLOOKUP with MIN

    Module 60: Watch Window In Excel

    My Notes