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.7 Using the AutoSum Command

At the beginning of this section we started talking about functions in general, and then we got into some function, and I mentioned early on that we are going to start out by doing things the long way.

First, we really started out by building a calculation that was like this cell plus this cell plus this cell and so on. That was very time consuming and potentially full of errors if we accidentally missed a cell or included too many cells.

It was very long and tedious process, then we brought into some function that streamlined it, but we still went about it the long way, I went into a cell I went to my formulas tab I went into math and Trig and I went and found the SUM function, that brought up the arguments window we filled it in and click OK, you know we went through six seven eight steps in order to get that sum completed.

We learned some key aspects along the way. But I want to streamline it even more, now that we got a good understanding of functions at least at the basic level. We can now start to use shortcuts.

Let us take-a-look at a feature called Auto sum. I want to automatically sum up a range of cells one button press done, and I am going to give you a couple of ways to do this.

First, I know we just did a lot of work here to set this up and get all those formulas, and then I am going to have you delete some of it.

I am going to grab cells B8 to E8, I am going to hold down my Ctrl key on my keyboard and I mean a slight E4 to E7.

Using the AutoSum Command 1

That is your Ctrl key and it allows you to select a group of non-contiguous cells non-contiguous rows or columns of cells.

That was my Ctrl key, I am on a Windows machine. if I remember correctly and I got to jog my memory do not work on a Mac all the time, but I believe you will hold down the Command key on the Mac to do that to build a select non-contiguous group of cells. If that is not correct, Jump into the review section. Let me know and I can go and figure that out for us. What I believe is it is Ctrl on the windows, and I believe it is Command on the Mac.

After highlighting, hit Delete key on your keyboard.

Using the AutoSum Command 2

Now I want to sum B8 back up. We saw the hard-core math approach this plus this plus this plus this. We saw the function approach which added a little more streamlined efforts made it a little more efficient for us. Now let us see the auto sum.

Click into the B8 cell. I am going to go to my Formulas tab and on the left, we have the Auto sum.

Using the AutoSum Command 3

Now if I just click on the Big E button the Sigma symbol there it will do it for me. I hit Enter key and I am done.

I could even select a range of cells which grab all three of those empty cells down there and I can hit the auto some button again formulas auto sum. Look at that auto sum does it all for me.

Using the AutoSum Command 4

Now this is one of the reasons why I like to start at the basics first. We are about to see something here that could potentially throw an error in your calculations, utilizing a more automated approach like the auto sum.

I am inside of E4, I want to get a total for rent across each of the months. I will go up to formulas hit the Auto sum and look at that sum B4 to D4, got all three of the values. Then Hit Enter key.

Using the AutoSum Command 5

Excel it is a very smart very intuitive application but sometimes it tries to assume too much, or it relies on its default settings to accomplish a task for us. And we need to be aware of what these defaults are especially when we are automating a process through a single button press.

How does excel know which numbers we want, Well it is based on the default behavior.

Again, it is a smart application, but it has some limitations and it has some defaults that are built into its behaviors.

So, this is not a big deal as long as we are aware of it and we recognize that something went wrong.

Using the AutoSum Command 6

So, all we need to do is instead of E4:E5 is I will go click and drag the proper range of cells, then hit Enter key to done.

If it is wrong not a big deal. Give it the correct range yourselves and you are done.

Try it out. Go through delete out the totals and repopulate. And just for click if you want some more practice with that button.

Go to the AutoSum button it is actually got two pieces to it.

If I click on the lower half I have got average the count function which is count numbers and Max and Min in there all through AutoSum button.

Using the AutoSum Command 7

So, try that one out, complete it. I know you have done it a couple of times but now you have got a nice little shortcut. It is complete that and then jump into the next lecture.