Microsoft Excel

2.1 Opening Microsoft Excel 2.2 Microsoft Excel Startup Screen 2.4 Introduction to the Excel Interface 2.5 Customizing the Excel Quick Access Toolbar 2.6 More on the Excel Interface 2.7 Understanding the Structure of an Excel Workbook 2.8 Saving an Excel Document 2.9 Opening an Existing Excel Document 2.10 Common Excel Shortcut Keys 3.1 Entering Text to Create Spreadsheet Titles 3.2 Working with Numeric Data in Excel 3.3 Entering Date Values in Excel 3.4 Working with Cell References 3.5 Creating Basic Formulas in Excel 3.6 Relative Versus Absolute Cell References in Formulas 3.7 Understanding the Order of Operation 4.1 The structure of an Excel Function 4.2 Working with the SUM() Function – Excel 4.3 Working with the MIN() and MAX() Functions 4.4 Working with the AVERAGE() Function 4.5 Working with the COUNT() Function 4.6 Adjacent Cells Error in Excel Calculations 4.7 Using the AutoSum Command 4.8 Excel’s AutoSum Shortcut Key 4.9 Using the AutoFill Command to Copy Formulas 5.1 Moving and Copying Data in an Excel Worksheet 5.2. Inserting and Deleting Rows and Columns 5.3. Changing the Width and Height of Cells 5.4 Hiding and Unhiding Excel Rows and Columns 5.5 Renaming an Excel Worksheet 5.6. Deleting an Excel Worksheet 5.7 Moving and Copying an Excel Worksheet 6.1 Working with Font Formatting Commands 6.2. Changing the Background Color of a Cell 6.3. Adding Borders to Cells 6.4. Formatting Data as Currency Values 6.5. Formatting Percentages 6.6. Using Excel’s Format Painter 6.7. Creating Styles to Format Data 6.8. Merging and Centering Cells 6.9. Using Conditional Formatting 7.1 Inserting Images and Shapes into an Excel Worksheet 7.2 Inserting Shapes In Excel 7.3 Formatting Excel Shapes 7.4. Working with Excel SmartArt 8.1. Creating an Excel Column Chart 8.2. Working with the Excel Chart Ribbon 8.3. Adding and Modifying Data on an Excel Chart 8.4. Formatting an Excel Chart 8.5. Moving a Chart to another Worksheet 8.6. Working with Excel Pie Charts 9.1. Viewing your Document in Print Preview 9.2. Changing the Margins, Scaling and Orientation 9.3 Adding Header and Footer Content 9.4 Printing a Specific Range of Cells 10.1. Intro to Excel Templates 10.3. Opening an Existing Template 10.4. Creating a Custom Template 13.1 Understanding Excel List Structure 13.2 Sorting a List Using Single Level Sort 13.3 Sorting a List Using Multi-Level Sorts 13.4 Using Custom Sorts in an Excel List 13.5 Filter an Excel List Using the AutoFilter Tool 13.6 Creating Subtotals in a List 13.7 Format a List as a Table 13.8 Using Conditional Formatting to Find Duplicates 13.9 Removing Duplicates in Excel 14.1 Excel DSUM Function Single Criteria 14.2 Excel DSUM Function with OR Criteria 14.3 Excel DSUM Function with AND Criteria 14.4 Excel Function: DAVERAGE() 14.5 Excel Function: DCOUNT() 14.6 Excel Function: SUBTOTAL() 15.1 Creating an Excel Data Validation List 15.2 Excel Decimal Data Validation 15.3 Adding a Custom Excel Data Validation Error 15.4 Dynamic Formulas by Using Excel Data Validation Techniques 16.1 Importing Data from Text Files into Excel 16.2 Excel 2019

4.1 The structure of an Excel Function

In the previous section of this book we talked about earlier, We began to talk about building calculations within Excel. And we saw just a simple little calculation rework, We were able to do some additions as far as referencing cells. We were able to take a cell reference such as before and then add that to cell reference B5 then add that to B6 and B7, We were able to do some addition.

You have got all your mathematical operators available to you. You have got the addition symbol you have got minus subtraction multiplication you have got division, you know your exponent you have got all those various operators that you can utilize to perform arithmetic type calculations math.

Built into the Excel application the nice people at Microsoft have built several functions, functions that we can then utilize to perform calculations.

And we talk a little bit about what Excel functions are how we can utilize them to help us become more efficient as we work with our data inside of Excel.

Excel Function: A predefined formula that performs a calculation

So, first off an Excel function really on a cell function is a predefined calculation.

Now the nice people at Microsoft have built a few hundred different functions for us to use, these predefined little packages that will do some type of calculation whether it is summing up a range of cells, it is finding an average within a range of cells it is looking up information based on criteria that we give the application it could be all slew of things, I want to be able to find my house payment or the next car that I buy how much am I going to pay for that monthly built into Excel , then have a function that will do that for you.

Now, in-order-for us to utilize a function, we need to really understand what the function is and what are the different pieces of a function.

So, let me give you an example here.

The structure of an Excel Function

This is going to be one of the first functions that we see inside of this book, this is the most common function that is used worldwide within Excel, this is the SUM function, here is an example.

This is taken right from the exercise file from the Monthly budget worksheet that we are creating together as a group.

The SUM function as you can see here, I want to build a sum up. I want to add up a range of cells well in order for me to do that we can do it like we did earlier, where I say equals this cell plus this cell plus this cell plus this plus the cell plus the right, I can do that and do that.

What if you have thousands of records and you want to add up you want to sum up all that data, I could take forever, Taping that out like we did earlier. built into Excel we got the SUM function that will streamline those efforts.

Now I want to break down functions in general, there is really three different pieces that we use as we build out an Excel function. We just saw a quick preview of some function both now, break it down into its three different pieces.

The first data and we have actually already seen this, every calculation from the simplest calculation to the most absurd complex were huge dynamic whatever huge function right or calculation they all start with the same thing from the most basic to the most complex they all start with the equal sign “=”

= FUNCTION NAME

The next step as far as using a function you then need to designate or denote which function you want to use. For example if I want to sum up a range of cells let us say = and I will bring in the SUM function, that is its name sum assume, if I want to find the average of a range of cells let us say =average.

The thing about learning functions, it is just that you got to learn the functions we have got to know about, there is a some function, there is an average function, there is a function for looking up information, there is a function for finding the minimum value from a range of cells or the maximum value from a range of cells. We got to learn what the functions are.

But once you got that down then there is one more piece, we got to bring in the function arguments or the parameters that that function needs.

So, let me give me an example of a complete function here.

The structure of an Excel Function

This is what we were looking at earlier, It is got all three pieces, It is got the equal side. Maybe that is a given you always do that with any type of calculation, then you got the function name, In this case you got some. And then the sum function specifically within the parentheses needs to know the range of cells, that you want to sum up in this case equal sum “=SUM”. Let us sum up cells B4 through B8 and as far as functions are concerned when you deal with a range of cells to say through like B4 through B8. We use the colon character B4:B8, that is your through that is your range of cells.

So that is really the three pieces: equals, the function name and any arguments or parameters that that function needs in-order-for it to do its job.

Remember these functions there are predefined the nice people Microsoft built them, they determine how to how we use them and what types of data it needs to do its job. That is where the arguments come into play.

Now the next thing I want to bring your attention to, and we are going to get familiar with Function Arguments window.

You click fx icon then select any function you want (example SUM) then click OK button

The structure of an Excel Function

This is called the Function Arguments window.

The structure of an Excel Function 4

This is a little window built into the Excel application that is going to assist us in utilizing these functions, especially when you are just starting out utilizing functions, whether it is the same function or something more complex like Index and Match or Vlookup, or some other more complex function, you are going to want some help.

So, this function argument window is going to come in very handy. Because it will kind of take your hand and walk you through the process and explain each step to you.

Now here I am looking at the arguments for the sum function. You can see number 1 and number 2 there, the number 1 and number 2 they are just expecting a range of cells. You want to sum what is it that you want to sum.

Here I just said I want to sum before through and recall in through B8.

The nice thing about this windows gets it kind of takes your hand watch it through the process, they give you some great information, they are going to tell you what the function does.

The next thing it tells you is what of those arguments, What is number 1 what is number 2.

It says number 1, number two and so on. You can have between one and two hundred and fifty-five numbers to sum or ranges of cells that you can sum up to under 55 that is quite a bit.

It is also down the lower left-hand corner of the window is a nice little link that will jump out to a Microsoft page that will give you some help about the active function. In this case if I click that link help on this function in the lower left it will take you to a Microsoft page that talks of all about these some function, super slick, Very helpful.

So, this is your arguments window again excels got all sorts of functions in there as you are learning them.

I highly suggest not only going through this book and learning the functions, I am going to walk you through but read through the window, get an idea of what they are all about and potentially what you are going to be using for and what you could use them for.

Now like I mentioned there are loads of functions inside of excel by the last the last number that I read as of the 2013 edition, We have actually a few versions later. Now there were 461 functions built into the Excel application.

Then Microsoft adds functions they remove functions. And typically when they remove a function they replace it with something else , something that is more streamlined more efficient does more for you, it is more accessible those types of things.

There are loads of functions inside of Excel.

The structure of an Excel Function 5

Here this is a part of the ribbon inside the Excel interface, this is the formulas tab. You will find the form is tab there at the top of your screen.

Here they give you what is called the function library essentially a bunch of little books where you can go find all these various functions.

Then we are going to take-a-look at this we are going to become familiar with it and how you can find the various functions that live inside of your copy of Excel, get ready for this, I am excited.

I love functions any way that is going to save me time and help me become more efficient as I am performing calculation great.

First we got to start out what is more basic type functions like some Min, Max, Average, Count those types of things and as you progressed later and later inside of this book, we are going to see more complex more advanced those dynamic functions, I spoke about earlier. Get ready for it, Let us jump into the next lecture.