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 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • 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
        • Pandas for Beginners
        • 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 Risk Management
        • 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 Advanced
        • Tableau Desktop
      • Python
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • 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 > How to Use Microsoft Excel 2013 Functions – Part 1

How to Use Microsoft Excel 2013 Functions – Part 1

One particularly useful element of Microsoft Excel 2013 Advanced is the Function, with this version containing numerous new Functions as well as improved existing Functions from prior Excel versions. The Results of Functions in Excel can vary depending upon the computer platform running Excel. A Help facility is included in Excel and is useful for finding Functions according to category as well as reading their descriptions. In addition to Functions, Excel also utilizes Arguments which are themselves used within Functions. Arguments can be expressed in multiple ways, most typically Actual Values, individual Cell References and Ranges.

Watch the free video here, transcripts for the entire video follow:


Facebook Linkedin Twitter

Do you need to learn Microsoft Excel 2013? Get 19 hours of Microsoft Excel 2013 training – click here.

Video transcripts:

Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to start looking at functions and in fact functions are the first area of Excel that we’re going to look at in detail in this advanced course.

Now I think it’s true to say that functions have traditionally been one of the main strengths of Excel since the very earliest versions. And in Excel 2013 not only do we have a few dozen new functions but we also have some great improvements to existing ones. Now the number of functions in Excel 2013 is in the hundreds and I’m certainly not going to go through all of them on this course, but I am going to start by pointing you at a couple of places in online Help that are really going to be able to help you to find the functions that you need.

So let’s start with this online Help entry, Excel Functions by category. Now in there we get a categorized list of the functions. With each of those if you click on the link it takes you down to the functions, a list of the functions, and with each of the items in the list there’s a description of that function. As I say I’m not going to go through all of these now. We’re going to use many examples on the course. Let’s go back up to the top of this page though. Right at the top here there’s a note that says Important and that’s exactly what it is.

 

Learn microsoft Excel 2013

 

Some of you will be using Excel on devices with ARM processors. So you may be running Windows RT using Excel. And in that case the level of accuracy, the calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC, a 32 bit or a 64 bit PC and a Windows RT device using ARM architecture. Now to be fair the differences are going to be very slight and they’re going to be in certain specific circumstances and there is a link here that takes you through to explain more about what those differences are. It’s also the case, although it’s not mentioned here, that some values generated by some Excel functions in Excel 2013 are actually slightly improved, slightly different from ones that are generated in earlier versions of Excel. And in the process of continual improvement of functions Microsoft have made some improvements to a number of functions. You may find that if you are using Excel 2013 on a device and you call a function to get a value and if you called an earlier version of the same function in an older version of Excel it is possible in some specific circumstances to get slightly different values even using the same PC. Now these differences are slight. They’re in very specific situations and to a large extent we can ignore them from this point onwards. In fact none of the examples that we’re using on this course should show any of these differences. However, if in your normal use of Excel you do find differences like this, perhaps you’ve got a particular complex calculation that you’ve been running in an older version of Excel for some time, you’re upgrading to Excel 2013 and suddenly the values you get just seem to be that little bit different from how they used to be, this could well be explained by changes to, improvements to Excel functions in Excel 2013. That’s something to be aware of but it’s not something that I’m going to go into any further on this course.

Now let me just point out one or two specific things about the Help facility here. As it says there, Worksheet functions are categorized by their functionality. If you know the category you can choose a category and find it. There’s also a Search facility here. As it says, Control-F brings up a Find function. Let’s suppose you’re looking for a standard deviation function, you can start typing Standard, S-T-A-N-D, and so on. And if in this case it’s found Stand in a standard normal cumulative distribution you could keep typing or you could just say well it’s going to be something like that. Let’s try Next, Next, there we are, Standard deviation. And in addition to this categorize list of functions if I just do a Back in Help there is a list, Excel functions alphabetical, click on that, and that gives a full list in alphabetical order and you can jump to a particular first letter by clicking on it in the this sort of index here.

So you’ve got a lot of functions in Excel 2013 but they can even be supplemented. You can either download or purchase Add-ins and of course you can write your own functions using VBA. We’re not covering VBA on this course but if you want to be able to pretty much write a function to do anything learning VBA is the way to go.

So let’s have a quick recap on a couple of syntax rules about functions. What I have here is a little scratch workbook. It’s got five cells with numbers in and I’m going to enter in the cell below that column of five numbers a function to total those numbers. Now if you have trouble with that you probably shouldn’t really be doing this course. But basically within a formula we’ll begin the formula with an equal sign and then we can specify the name of the function that we want to use. We can select it. There’s a number of ways of doing it but let’s suppose I know I’m just going to add those up. I put in the Sum function. That’s a function you should certainly know. Then always there are parentheses. Even for functions where you don’t need to do anything more than know the name of the function you will always have parentheses, open parentheses, close parentheses. And as soon as I type the parentheses there I’m given a sort of hint which basically gives me a list of arguments. Now I’m going to talk a little bit more about arguments in a moment but basically in this case you can see you’ve got a bold argument which means there must be at least that first one. And then a comma, arguments are separated by commas. And then the later ones which are in brackets are then optional. Now I could if I wanted to in that particular function list of arguments there I could put something like 3.0, comma, 5.2, then close the brackets, and tick to enter that function name and there we are, 8.2. Of course it’s got nothing to do with the five numbers above it but you can put literal arguments in, actual values in there. It’s not something you’ll often do but there’s nothing to stop you doing it.

So let’s now work towards summing those five numbers. I’m going to dwell on this a little bit because it’s important to understand some of the rules about arguments.

 

Simon Sez IT MS Access 2013 training course

 

Let me click back into that Sum function again, into the list of arguments, and this time I’m going to delete the arguments that were there before and instead of those I’m going to put in the cell references and I can always use cell references. And as I put in the cell references note the coloring of the cell references, the coloring also relates to the coloring on the border of each cell in the worksheet. So I’ve got a blue C2, a red C3, and so on. And I can list the cell references. Again tick it and now I’ve got a much more sensible use of a function. So the function arguments can certainly be cell references.

Now let’s suppose that I really want to do is to put in a range. And a range is very often what I will be summing. So let me suppose I put in sum C2, as you should know, colon, C4 specifies a range. Notice how when I do that I have the range highlighted in blue covering those three cells. Also note the little selection handles at the four corners of that cell range. If I hover over the bottom right hand one I can adjust the cell range using the mouse and you’ll see that reflected in the formula in the entry bar. And that’s probably something that you use regularly all ready. Again when I’ve finished I’ve got a sum C2 to C6, tick in the entry bar, and now I have the arguments expressed as a range.

And let me just demonstrate this once. I won’t keep doing this but I think it’s useful for those of you who are using touch to see that it works pretty much the same way. If I switch into touch mode and then just select the cell there with the formula in it you can see how I get those round handles for touch mode. Keep an eye on the formula in the entry bar. I’m using my fingers now to change that selection and as I change the selection you’ll see it change in the entry bar as well and you can see the markers where my fingers are on the screen.

So I’ve shown you there the three most common types of argument that you’re going to use in a function, so actual values, individual cell references, ranges. Of course you can also have references to other sheets or even other workbooks within a function. Let’s look at a couple of special cases here. Can you think of an example of a function with no arguments? Well here’s one, pie, tick, and that gives you the approximate value of pie. And also one of the main syntactic rules is don’t have a space between the name of the function and the parentheses. So if I type in here Equal sum, space, open brackets then I can type in something similar to what I did before but I just get an error message because that space stops Excel 2013 from seeing that that is one of its functions, so I’ll just get an error message.

Another very important point with functions is that you can nest functions. So for instance in the sum function here I could say comma, pie and what that would do is to say calculate the sum of cell C2 to C6 and pie. Note I must have the parentheses with pie for it to be recognized as a function. Tick that and that’s the sum of those five numbers and pie.

So that’s a pretty quick recap of the basics of using functions. If you haven’t used formulas and functions a lot before there are a couple of very useful Help pages that you might want to work through. One of them is Overview of formulas and that gives you some good examples of the use of formulas, explains some of the things that I’ve done about using functions, constants, cell references, and so on. If you’re not comfortable with those I think it’s a really good idea to go through that. And then another very useful page is this one and I suppose that to some extent this page is aimed at the opposite end of the function spectrum in terms of knowledge. This is really meant for people who’ve used functions a lot in the past and your question is, So what’s new in Excel 2013? Well there is a Help page dedicated to which are the new functions in Excel 2013. So that’s another one that’s well worth looking at.

And for those of you who are really hot on functions this is also a very important page in Excel 2013 Help as well because it lists compatibility functions. These are functions that have actually been superseded by newer functions, better functions, but they’re still provided in Excel 2013 for compatibility reasons. So if you want to make sure whether any of these functions maybe you use all ready there’s a better alternative than your alternative or just to understand what’s changed then that’s an important list for you as well. And as the reference says here, For more information about the functions that are replacing these compatibility functions see Statistical functions reference. So particularly in relation to these types of statistical function then looking at what they’ve been replaced by is pretty important.

So that’s it on the basics of functions and what we’re going to look at in the next section is some of the more advanced techniques for working with functions before we move on to some case studies and applications. So I’ll see you in the next section.

 

advanced Microsoft Excel 2013 training course by Simon Sez IT

 

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 How to Use Microsoft Excel 2013 Functions - Part 1 ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

Preparing Payroll Tax Forms in Quickbooks Pro 2012

How to Insert PDF into PowerPoint? 3 Easy Ways

How to Use the Excel Collapse Rows Feature? — 4 Easy Steps

How to View and Navigate Pages in Publisher 2013

Complex Examples of Formulas in Microsoft Excel 2016

How to Use Timed Slides for Your PowerPoint 2013 Presentation

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)