306 Microsoft Excel – Intermediate Functions and cell referencing

Microsoft Excel – Intermediate Functions and cell referencing

Number and percentage formatting: Understanding the various ways of formatting, including the understanding form percentages to better use them in formulas and functions.

Understand that cells can be formatted (even without data in them) so that when you enter data, it can look completely different to what you expected to see. Sometimes cells can be formatted in such a way that even though you enter numbers, a date may be shown. Learn about why in this section.

Relative and Absolute Cell references: Understand how relative cell referencing works to help you copy formulas and functions quickly. Also understand where they don’t work and why. Absolute cell referencing is a powerful way to reduce the amount of calculations you have to create and can save several columns or rows of typing. Learn the different types of and how to use absolute cell references in this section.

Learn and understand some fundamental mathematical functions that will help you perform most formulas and functions. These are fundamental skills that will help make more complicated functions easier to understand.

Understand how to combine two formulas into one and as a result write much better formulas and get to know the order of mathematical calculations.

Using Functions like Maximum, Minimum and average

Getting to know the “Insert Functions dialog box”, how to search for a particular function and understand the concept of “arguments” (or syntax) in functions and function categories such as financial, Date and time, maths and trig, etc.

Understanding and constructing an IF Statement.

Comments: Understand the uses for comments. Insert comments that help to explain the purpose of a cell, choose the size and location of the comments and control the way they appear on the spreadsheet. Edit comments and change the formatting within a comment, change the name of the person who made the comment, and make sure that the comment appears all the time or only when you move the mouse to it.

Show/Hide Columns/Rows: Learn how to hide columns or rows that contain confidential information. This way you can print the spreadsheet out without the confidential information, but use the confidential information to perform the necessary calculations.

Training Video List

Section 1 videos

Number and percentage formatting: Understanding the various ways of formatting, including the understanding form percentages to better use them in formulas and functions. Understand that cells can be formatted (even without data in them) so that when you enter data, it can look completely different to what you expected to see. Sometimes cells can be formatted in such a way that even though you enter numbers, a date may be shown. Learn about why in this section.

306101 – Cell number formatting (Length – 2:18)

306102 – More on number formatting (Length – 1:41)

306103 – Percentage formatting (Length – 2:28)

306104 – More about percentage formatting (Length – 2:27)

306105 – Using percentage in formulas (Length – 1:47)

Section 2 videos

Relative and Absolute Cell references: Understand how relative cell referencing works to help you copy formulas and functions quickly. Also understand where they don’t work and why. Absolute cell referencing is a powerful way to reduce the amount of calculations you have to create and can save several columns or rows of typing. Learn the different types of and how to use absolute cell references in this section.

306201 – Relative Cell references (Length – 2:36)

306202 – Where Relative Cell references don’t work (Length – 2:39)

306203 – Absolute Cell references (Length – 2:49)

306204 – Absolute Reference options (Length – 2:00)

306205 – Absolute Row Reference (Length – 1:39)

306206 – Formulas using Absolute Row Reference (Length – 2:28)

Section 3 videos

Learn and understand some fundamental mathematical functions that will help you perform most formulas and functions. These are fundamental skills that will help make more complicated functions easier to understand. Understand how to combine two formulas into one and as a result write much better formulas and get to know the order of mathematical calculations. Using Functions like Maximum, Minimum and average

306301 – Formulas – increasing by percentage (length – 2:36)

306302 – Formulas – mathematics rules (Length – 2:51)

306303 – Formulas – adding multiple ranges (Length – 2:55)

306304 – Formulas – adding multiple ranges using keyboard (Length – 1:23)

306305 – Formulas – Average and Maximum (Length – 2:02)

Section 4 videos

Getting to know the “Insert Functions dialog box”, how to search for a particular function and understand the concept of “arguments” (or syntax) in functions and function categories such as financial, Date and time, maths and trig, etc. Understanding and constructing an IF Statement.

306401 – Functions – Insert Functions dialog box (Length – 3:43)

306402 – Functions – Function Arguments dialog box (Length – 3:42)

306403 – Functions – Function Arguments more info (Length – 2:28)

306404 – Functions – The IF Function (2:54)

306405 – Functions – Constructing an IF Function (Length – 3:54)

Section 5 videos

Comments: Understand the uses for comments. Insert comments that help to explain the purpose of a cell, choose the size and location of the comments and control the way they appear on the spreadsheet. Edit comments and change the formatting within a comment, change the name of the person who made the comment, and make sure that the comment appears all the time or only when you move the mouse to it. Show/Hide Columns/Rows: Learn how to hide columns or rows that contain confidential information. This way you can print the spreadsheet out without the confidential information, but use the confidential information to perform the necessary calculations.

306501 – Misc – Understand and insert Notes (Length – 2:15)

306502 – Misc – Editing Comments (Length – 1:25)

306503 – Misc – Showor Hide Comments (Length – 1:24)

306504 – Misc – Formatting Comments (Length – 1:03)

306505 – Misc – Hide & Unhide columns and rows (Length – 1:39)

 

Back to Microsoft Excel Training Courses outline Enrol now

 

The Australian Small Business Training and Support

Subscribe to our business blogCourses, Training and Support.

Get bookkeeping, marketing, operations, customer service, sales and finance knowledge via email. Keep up to date with new and emerging trends and technologies and get our free samples.

Enquiry Form

  • This field is for validation purposes and should be left unchanged.
Call Now Button
%d bloggers like this: