OBINTUSACADEMY
HOME CONTACT SERVICES PRIVACY
COMPUTER BASICS MS-PACKAGES
Excel - References

MICROSOFT EXCEL

MODULE ONE

Prerequisite Introduction to Microsoft Excel Getting Started MS Excel Environment Title Bar Quick Access Toolbar Search Account Name Buttons Menu Tab Ribbons Dialog Box Launcher Name Box Insert Function Formula Bar Worksheet Area Worksheet Navigation Creating New Sheet Status Bar Worksheet View Format Zoom Slider MS Excel File Tab Entering Data Saving Your Workbook Opening Saved Workbook Copy, Delete and Hiding Sheet Closing Entire Workbook

MODULE TWO

Cells, Rows and Columns Resizing Rows and Columns Inserting Data Select, Delete and Move Data Copy, Paste and Cut Find and Replace Checking Spellings Inserting TextBox Inserting Symbols Inserting Special Characters Zooming Undoing and Redoing Changes

MODULE THREE

Mini Toolbar Setting Fonts Text Decoration Setting Text Case Text Alignments Text Orientation Increase and Decrease Indentation Wraping Text Merging Cells Cells Borders Cells Shades Copy Formatting Insert and Delete Worksheet Format Worksheet, Cells, Rows and Columns

MODULE FOUR

Insert Tables Insert Pictures Insert Shapes and Smart Art Insert Screenshots Insert Icons Insert 3D-Models Using Add-ins Insert Charts 3D Maps Creating Sparklines Insert WordArt

MODULE FIVE

Addition Subtraction Multiplication Division Using Formulas Using Functions Quick Functions

MODULE SIX

Fill Handling Sorting Filtering References (Relative and Absolute) Conditional Formatting Applying Styles

MODULE SEVEN

Page Themes Page Colors Page Fonts Page Effects Page Header and Footer Page Numbering Page Margins Page Orientation and Size Print Area Page Breaks Setting Background Print Titles Preview and Print Share Workbook Freezing Panes Range Data Validation Excel Templates MS Excel Shortcuts Conclusion

HOW TO

Excel How-To Send Email Separate Text Date and Time Format Macro Thesaurus Insert Comments Password Workbook Count Characters Count Words Add and Remove Hyperlinks Insert PDF in Excel Editing and Formatting Restrictions How to Compare Columns How to Compare Worksheets Add Signature in Worksheet Calculate Age Calculate Mean Calculate Median Calculate Standard Deviation Find the Mode Add and Remove Commas How to Remove Filter Convert Excel to CSV Excel to PDF Excel to Ms-Word Document Excel to Picture Formats Duplicate Rows and Columns Hide and Unhide Rows and Columns Rows and Columns Conversion How to Remove Gridlines Create Graphs Insert Check Box Add and Remove Watermark Logical Operators Logical Functions Text Functions Square Symbol Pi Symbol Selecting Different Columns Creating QR Code Creating Bar Code Insert Bullet Points Generate Random Numbers Combine Two Columns Wrap Text Create Dropdown List

References

PREVIOUS NEXT

References in Excel are majorly used in formulas to perform calculations which can auto fill upwards, downwards and sidewards following the function used.
There are three types of references in excel, they include: Relative References, Absolute References and Mixed References. In this tutorial I will be showing you step by step how they are used.

  • RELATIVE REFERENCES

    • Relative reference is whereby the rows or columns changes when you copy a formula of another row or column to perform the same calculation. For example, A1 * A2 (formula for addition), when copied to another row and column it changes to the row and column (e.g. C1 * C2) name and perform the same multiplication operation.

  • Step 1 - I will be using the data in the image below for this illustration. It contains the prices of gadgets which will be multiplied by the quantity to give final price for each purchase.
    • relative-references
  • Step 2 - Write a formula to multiply the price and the quantity. Do this for the first gadget which is the Laptop.
    • relative-references
  • Step 3 - After pressing enter, the final price result will be displayed.
    • relative-references
  • Step 4 - Now to reference this to the second column (Speakers), click on the final price result cell of the (Laptop) and move your mouse pointer to the bottom right edge of the cell, click on the small box there (notice your mouse pointer changes to a plus [+] sign) and then drag it onto the next cell for the speakers column to be calculated.
    • relative-references
  • Step 5 - This final price result for the Speaker column will be calculated with the same formula as the laptop but using its own different cells.
    • relative-references
  • Step 6 - To confirm this, simply take a look at the formular bar and what you will see is the formular
    • relative-references
  • Step 7 - Click on the formular bar and the formula will appear in the cell
    • relative-references
note
NOTE:

The example given above can be applied to other situations that requires relative referencing.

  • ABSOLUTE REFERENCES

    • Absolute reference is whereby the rows or columns do not change when you copy a formula of another row or column to perform the same calculation.

    Absolute reference is denoted with a dollar sign before the cell letter and before the cell number (i.e. $A$1, $B$1 etc). For example, $A$1 * A2 (formula for addition), when copied to another row and column only A2 will change but A1 remains the same (i.e. it now becomes A1 * C2) and perform the same multiplication operation.

  • Step 1 - I will be using the data in the image below for this illustration. It contains the prices of gadgets, quantity and the total price. The last column named 30% will be the amount that will remain if a discount of 30% is removed from the total price of the gadgets.
    • absolute-references
    note
    NOTE:

    The total price in the above image is calculated using by relative reference.

  • Step 2 - Now I am going to write the formula to calculate the subtract the total price from 30% of the total price (i.e. total - [30% * total]). But note that I want 30% to be constant, I would have to put the dollar sign $ in the before the cell letter before the cell number that contains the value.
    See the formula in the image below.
    • absolute-references
  • Step 3 - After typing the formula once you press the Enter key on your keyboard the inputs will be calculated and the result will be displayed.
    • absolute-references
  • Step 4 - Now move the mouse pointer to the bottom right edge of the cell, click on the small box there (notice your mouse pointer changes to a plus [+] sign) and then drag it down to the rest cells and it will auto fill them displaying the results calculated from the formula given.
    • absolute-references
  • Step 5 - Result has successfully auto-filled the rest cells.
    • absolute-references
  • Step 6 - If you click on any of the result cells and check the formula bar, you will notice that the only thing that changed was the cell which did not contains the dollar sign ($).
    • absolute-references
  • Step 7 - For clear view, click the formula bar and the formula will be displayed in the result cell as shown in the image below.
    • absolute-references
tips
TIP:

For quick remembrance, take absolute reference like a constant in maths that usually doesn't change. And it has to begin with a dollar sign before the cell letter and number.

  • MIXED REFERENCES

    • Mixed reference is simply the combination of relative and absolute reference. You can try this on your own, get a table data that its calculation contains data that need to be calculated but has a constant value in its operators.

PREVIOUS NEXT
ADVERTISEMENTS

LEARNING IS A CONTINOUS PROCESS - PRACTICE MAKES PERFECT