Excel Functions


Excel Basics

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

5.2. Inserting and Deleting Rows and Columns

We have seen a little bit of copy and paste. what about adding additional rows and additional columns into an already built spreadsheet.

If I want to introduce an additional bill into my monthly budget. You can do this a couple of ways. There are a few things that we need to be aware of.

First, I want to introduce a new bill in here so I need a new row for that new bills I could put the numbers in there and the calculations, then the calculations.

First, I need to do is click on the row 7 to select a row, just the header right there on the left click on row, This will highlight the entire row. (you can hit Shift Spacebar)

Inserting and Deleting Rows and Columns 1

I hold down the control key and on my number pad I am going to hit the plus (Ctrl +). or you can Right click then select Insert command, and this will insert a new row.

Inserting and Deleting Rows and Columns 2

Now, I could put in new bill. Well we got water, I got to pay for the water bill, I would drop in my label, I am not going to drop in some numbers. Watch bottom calculations at row 11.

Now, I could put in new bill. Well we got water, I got to pay for the water bill, I would drop in my label, I am not going to drop in some numbers. Watch bottom calculations at row 11, my formula automatically updated.

Inserting and Deleting Rows and Columns 3

That happens because I added that row somewhere within that dataset. If I am outside of that data set like I added up above the table of book Excel is not going to get.

You put it someplace in between that existing set of data than the formula automatically updates.

So, I can insert a new row drop in my values and just watch your calculations. These bottom ones automatically updated these I just copy paste or auto fill them down and I am done.

Now the same is true for a column, I need to add the next month.

First, I select F column by click on header column or hit Ctrl and Spacebar on your keyboard, then R-click and select Insert command or hit Ctrl +

Inserting and Deleting Rows and Columns 4

I can start dropping my numbers, all these calculations should have updated includes numbers in April. Copy and Paste formulas in G column and row 11 for it.

Inserting and Deleting Rows and Columns 5

So, adding row adding columns, I use shortcuts highlight the row or highlight column control + on your number pad or control shift + for the top plus sign, you have also got interface tools you can use.

I go to the Home tab, I go over towards the right. You got a section called cells and inside of there you have got inserts, I would go to insert here, and I got the same options. I can insert rows I can insert columns. You have got the options right there and like shortcuts right control +

Inserting and Deleting Rows and Columns 6

Now, I no longer need water row. I can highlight row 7 again and this time rather than control plans to add a new row, I am in a press control minus () and that I will delete the water row.

Inserting and Deleting Rows and Columns 7

Let us get rid of April. I highlight F column and hit Ctrl minus(-) to delete column.

Inserting and Deleting Rows and Columns 8

Again, if you do not have the full number pad you can do control and minus at the top of your keyboard. No shift key on that, one just controls in the minus symbol.

You have multiple ways to do just about everything, But I love shortcuts. Just keep in mind as you add rows and columns to an existing set of data that you watch the formulas, Make sure that. They are updating or in some cases, you have to populate them yourself.

So, try it out add some additional months add some additional rows whatever you like to do.

This is also a good spot within our book to take a moment and take a snapshot of your document. If you are not getting too personal with your data take a snapshot. Go to the review section of this book on amazon and drop that snapshot in there. Let us see what you have done so far, Let us celebrate what you are learning inside of Excel. It is exciting, there is so much that you can do inside of this application.

Again, we have only gone through a little bit of the book. There is so much more you can do inside of Excel and streamline your efforts and become more efficient as you work with your data inside of Excel.