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

3.6 Relative Versus Absolute Cell References in Formulas

How’s it looking for you so far, You got your calculations, I have got my total row, Got the total for each month and I have got my total column in there my total for each bill, as well is a grand total for all the bills for all the months.

If using your own numbers, Just be aware of that, Do not try to compared against mine. If you are using my exact numbers make sure everything’s looking good in here.

Relative Versus Absolute Cell References in Formulas 1

If I got something wrong right let me know, jump into the review section let me know I got one of them wrong.

If I got something wrong right let me know, jump into the review section let me know I got one of them wrong.

So I want to take this idea of cell references and formulas a step further, We are going to do a couple of things here, but the topic that we are going to get into is a difference between our two reference types, because we actually have two different types of ways or methods that we can use to reference cells inside of an Excel worksheet, We have what is called relative and absolute.

So, the first one a relative reference versus the second one is an absolute reference or I have also heard it called a fixed reference. So, what is the difference?

Let us go ahead and delete out the formulas that you just create, I am going to delete them all except the first one The January total. So, let us delete out all that hard work you just did.

Relative Versus Absolute Cell References in Formulas 2

However, it is going to be I am going to show you even simpler ways to do this, but again building these key concepts.

So, our first formula if I get them on a double click at cell B8 we have four-cell references (B4, B5, B6, B7). We are just adding them up, Adding them up giving us a total sum.

Relative Versus Absolute Cell References in Formulas 3

These reference types these are called a relative reference. What is a relative reference?

A relative reference means from this location you are going to get cells B1 B2 B3 B4. You are going to get these four cells right here right B7 cell. Now let us take this step further.

I want to get February’s total but this time instead of the B cells it should sum up the C cells.

To copy: press Ctrl + C at cell B8, then I go over to the C8 and paste it in Ctrl+V to paste.

Relative Versus Absolute Cell References in Formulas 4

Now I have got my total for February.

How did it know which cells to sum up? It knew because it was a relative reference the formulas is same, if I give it a double click we still got the equal sign we still get the plus side but the column changed it is now C4,C5,C6, C7.

Relative Versus Absolute Cell References in Formulas 5

So now we are just saying copy that from B8 to C8.

I am going to copy this one Ctrl+C over to the empty cell for March.

It performs the same formula but relative to its location. I am just in a new location new cell right but it is always five cells up that is a relative reference.

Relative Versus Absolute Cell References in Formulas 6

Or whatever direction.

You do that again copy, paste relative references for cell E4,E5,E6,E7,E8.

Relative Versus Absolute Cell References in Formulas 7

You have got consistency in the layout of your document.

Saves so much time and this is relative references.

Now, remember I mentioned that there are two different types of references that was relative and that is what we have got by default, but you also have what is called an absolute reference.

I am going to hop over to the F column F three and I am going to tape in Percent and hit Enter key, So now what I want to do here is I want to perform a calculation. We are going to do some division here that will tell me what percentage each of my bills is of my grand total.

Still a formula you start all your formulas with the same character and say equal “=”  then I am going to say E for this time I am actually just going to click on E for at that puts it in F4 for me so equals E for divide and I mean to use the backslash as the division character and then I am going to go grab E9. So, I am taking E four and I am going to divide that with E9, backslash forward slash.

Looking at your number pad, that is actually your forward slash.

You want to use the one off your number pad if you do not have a phone number pad then you are going to look at your keyboard and it is with the question mark key. So, you want that slash right there that is your forehead slash right backslash for it slash. We want the forward slash, so E4 Divide that by E9 then hit Enter key.

Relative Versus Absolute Cell References in Formulas 8

So do not give me exactly what I am looking for but it gave me a percentage. It decimal percentage so roughly seventy two percent. seventy percent of my grand total goes to rent.

Now what type of reference is this, You get that double click, you see E4 E8 these are relative references. Now I want to do that exact same thing but for a phone and then I want to do it for food and then I want to do it for candy and so on. I jump up I grab that formula, I copy Ctrl+ C, I dropped down a cell I pasted in Ctrl+V.

Relative Versus Absolute Cell References in Formulas 9

What is that? Slash zero, exclamation mark, what went wrong?

What is going on, well remember there is two different types of references, here We are using a relative reference,

From F4 we are going to go one cell to the left that is E4 and then we are going to go four cells down and we are going to divide with E9.

So now I copy that down and I got an error. Let us give F5 a double click (or hit F2), what is going on here.

From F5 we went one cell to the left, and then we went four cells down because the first one went four cells, so when we copied it down it still went four cells.

Relative Versus Absolute Cell References in Formulas 10

This time we need it to go one cell to the left but then always go to E8, that should not change.

So this is where the absolute reference comes into play, I am going to go back to my first formula at cell F4 and I am going to make a change to the E9 reference, I am going to make it an absolute, So I am going to put a dollar sign before the column reference E and we put a dollar sign before the reference 9 ($E$9) (or hit F4 on keyboard), That makes E9 that cell absolute. then hit Enter key.

Relative Versus Absolute Cell References in Formulas 11

Note: You can hit F4 to quick change the absolute reference address of the cell.

I am just going to copy that Ctrl+C and I will paste (Ctrl+V) it in F5,F6,F7,F8 cells.

Relative Versus Absolute Cell References in Formulas 12

Grand total of 100 percent.

Now each of these formulas is now referencing E8.

Relative Versus Absolute Cell References in Formulas 13

You copy that formula anywhere does it matter where they take that? You got a zero, because it is not doing much math for you, but it is still E9.

So, you got relative references really just based on its position it is going to move in a specific direction. Many cells and you got absolute references that absolutely reference this cell and remember the dollar signs ($), You have got your absolute reference.

So, again just like every time practice try it out jump into your own version create a percent column. Make sure you got all the other formulas in there because you are going to need those and then create your percent and find out what percentage each bill is of your grand total. And remember to streamline it make the absolute reference copy paste.