Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop
      • Python
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > Basics of Formulas and Functions in Microsoft Excel 2016

Basics of Formulas and Functions in Microsoft Excel 2016

During this Microsoft Excel 2016 training tutorial video, discover the essentials for using formulas and functions. Your instructor will demonstrate the general principles of doing calculations in Excel 2016.


Facebook Linkedin Twitter

Like what you see? Get our complete Microsoft Excel 2016 training courses for beginner, intermediate and advanced learners. Click here to learn more. >>

Video Transcript

This is the Business Expenses worksheet so far and how you found out quite a bit about formatting a worksheet and making sure that everything is starting to look pretty good. What we’re now going to do is to turn to what is probably Excel’s greatest strength and probably the reason that most people use Excel which is to do some kind of calculation or analysis. Whether your strength is in calculation or whether you’re doing it somewhat reluctantly I think you’ll find that many of the things that you need to do in Excel are pretty straightforward and we’re going to start with one or two really straightforward examples of calculations in Excel. And in doing that I’m going to try to demonstrate the general principles of doing calculations in Excel 2016.

So the first thing I’m going to do is to apply a little bit of Zoom. Now let’s do some work on the total amount of Toby’s Expenses over the period that is in that table of values.

What I’m going to do is to put the total in this cell here, F9. And first of all I’m going to do this whole thing pretty badly or at least you’ll see in a few minutes time that it was done pretty badly. And that is I’m just going to put a simple formula in here to add up the figures in column F. Generally speaking if in a cell rather than putting a number like an amount of currency or some text like the word Travel or a date you want to put a formula that something that involves a calculation you put an equal sign and that’s how you always begin cell contents where there’s a calculation to do, so equals. Now you actually enter the formula. Now in this case what I want to do is this. I want the contents of F4, so that’s F4, and I just type F4 and then I say plus and then I’ll want F5 and then I want and so on. So let me just put the others in and that’s a Formula.

Now at the moment in the cell you can see the formula but if I enter that value now either by hitting the Enter key or using the Enter tick mark by the Formula Bar you’ll see what happens. I actually get the total amount of the expense claim over that period.

Want More? Get Started With a Free Excel 2016 Course! Click Here

Now before we go any farther there’s one very important to recognize here and that is that cell F9 is now unlike the other cells on this sheet because it has both a value, it’s value is $305.20 and it has a Formula. And its formula is the formula in the bar. So when you click in the Formula Bar, you see in the Formula Bar the formula which you also actually see in the cell in that situation. But if you select the cell it’s the value that you see.
So cell F9 has got a formula and a value. And closely associated with that is the fact that without changing the Formula the value may change. So let’s suppose that I spot an error in my expenses. Let’s suppose that the error I spot is that that Annual Subscription $64.50 should actually be $84.50. So there was a mistake of $20 right there. Let me change it to $84.5, tick. Now automatically the value in F9 is increased by $20. And when you make a change to a cell in Excel any consequential changes are automatically carried out. So in this case when I change the value in F7, F7 is part of the Formula for F9 so the value of F9 is recalculated as well. So we haven’t actually changed the Formula in F9 but the value changed because of a change in the value in the cell F7.

Now the Formula you can see here in F9 is a pretty straightforward Formula. It’s a Formula to add the contents of five cells together. And of course in Excel formulae can become extremely complex. And if you look at say the length of the Formula Bar there right across my screen you could have that Formula Bar full and more than full in terms of the complexity of the formulae that are possible in Excel. But apart from writing out a sum like this, =F4+F5+F6, etcetera there are some other ways of doing things like totaling a column of figures that are actually much more efficient ways of doing things. And I’m going to demonstrate one of those to you next. But just before I do I want to point out one other thing and this relates to something that I said earlier as well.

If I were to put an additional expense in here. So let’s suppose that I realize that there was another expense for the 10th of March, March the 10th but I’d forgotten to include. Let me select row 7 and Insert an additional row. Let’s go back and see what the formula is in what is now F10. Look at the formula. It’s F4+F5+F6+F8+F9. What Excel can do is to compensate for things like inserting and deleting rows and columns. So not only do you not need to worry about that because Excel takes care of it but obviously you can use that to your advantage.

Now let me just Undo that and I’m actually going to Delete F9. So don’t forget when I’ve got that cell selected I can either just press the Delete key or I could say Clear All because clearing all will clear formulas and it will clear contents and it will clear formats. It’ll clear absolutely everything. So that’s all cleared. And now I’m going to take a different approach to adding up that column of figures.

And the approach I’m going to take is this. I’m going to select F4, keep the mouse key down, drag down to the bottom, and just include F9, then I’m going to release the mouse key. Now I mentioned this little icon down here before. Ignore that for the moment, the Quick Analysis icon. Don’t worry about that for the moment. What we’re going to do is to go up to the Editing Group on the Ribbon and next to the AutoSum button we’re going to choose Sum. And unsurprisingly the total we get is back to $325.20, the same as before. And we have a Formula in F9 but it’s a very different formula to the one we had before because it now uses the Excel Function of Sum.

And the general format for a Function is that you’ll have the name of the function, in this case Sum, then you’ll normally have a set of brackets and then in this case there is what’s called a Range of Cells. So it’s a range from F4 to F8 and the ranges indicated by a starting cell, then a colon, then the ending cell in the range. Now in this case those two cells are in the same column but in fact they could be in different rows and columns. So a range is actually a rectangle of cells. And in this case the top left of the rectangle is F4 and the bottom right of the rectangle is F8. So we have a little sort of sub-column of cells. So in that formula we’re using the function Sum.

Now one of the big advantages of using the Sum function there is you haven’t got to type in the list of cell names. But there are some other advantages to it as well and let me do what I did just now and insert a row corresponding to a missing expense. So let me select row 7 again, do an Insert and let’s now look at that formula. And the formula is still just =Sum but in this case not only does it cover the correct range from F4 to now F9 but if I put a value in here, let’s say I put a value in there of say $17.30, it would be included in the calculation because by using that function I didn’t have to specifically mention the new F7. All I needed to say was the range and F7 is automatically included because it is in that range. So that’s a big advantage of using a function here.

Now let me just Undo that last couple of operations. And the other thing that is particularly useful about this approach, let’s suppose that instead of adding up those expense amounts I wanted for some reason to find which the largest was. If I click within the Formula Bar and delete the word Sum and instead change it to the word Max what I’ll see there is the Max figure in that column. So the maximum amount is $127.50. Or what about the average figure? Now as I start typing that function name a whole list of the functions that begin with the letter A appears. And at this point if I wanted to just scroll down, find the one I want, Average, that’s the one I want. Let’s take that, tick that. The average expense amount is $65.04. In Excel 2016 there is a very, very large number of Functions available.

Want More? Get Started With a Free Excel 2016 Course! Click Here

So in this section I’ve introduced you to the basics of Formulas and Functions. In the next section we’ll start looking at some more complex calculations and how to set those up efficiently and effectively in Excel 2016. So I’ll see you then.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

START LEARNING Basics of Formulas and Functions in Microsoft Excel 2016 ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

How to Make a Scatter Plot in Excel? 4 Easy Steps

Formatting Date and Time in Excel 2010

Tips and Tricks for Microsoft Word 2010 – Part 3

Receiving Inventory in QuickBooks 2019

How to Create a Database in Microsoft Access 2013

Using the Loan Manager in QuickBooks 2019

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)