Cell Reference in Excel

                      Cell Reference in Excel

A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate.

 

Knowing the cell reference will make our task of writing complex formulas much simpler.Excel uses two types of cell references to create formulas.

Relative and Absolute Reference

 Relative references: are basic cell references that automatically adjust to new locations when the formula is pasted into different cells.

Absolute Reference: Sometimes, when you copy and paste a formula, you don't want one or more cell references to change. Absolute reference solves this problem. Absolute cell references in a formula always refer to the same cell or cell range in a formula. If a formula is copied to a different location, the absolute reference remains the same.

An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference or the row reference, or both. Examples of absolute referencing include:
 

 

To Create an Absolute Reference:
Select the cell where you wish to write the formula (in this example, H2)
Type the equal sign (=) to let Excel know a formula is being defined.
Click on the first cell to be included in the formula (F2, for example).
Enter a mathematical operator (use the multiplication symbol for this example).
Click on the second cell in the formula (C2, for example).
Add a $ sign before the C and a $ sign before the 2 to create an absolute reference 

Shortcut tips

When writing a formula, use the function key F4 to change the reference from relative to absolute and vice versa.

Happy Learning - Excel Mania

             

Tips to reduce the Size of the Excel File

1. Using of the same file again and again: For example: Working on the weekly Report and instead of creating a new report. We use the same report and only alter the data as per the current week and save it. This will increase the internal memory and will increase the excel file size

Solution: Keep a Blank Template and paste the data in the blank Template and not in the prior weeks report. This will help maintain the excel size

2. Sharing of Excel workbook and saving it only after a long interval also increases the excel memory and than inturn increases the size of excel

Solution: Save the file frequently, Ensure the Workbook is closed and if there is no requirement to share the file forever. Then unshare the file and save it.

3.Here are few more methods to resolve this

a.Delete un-necessary empty sheet(s). 
b. Delete un-hide sheet(s). 
c.Delete the un-used transparent data cells which doesnt have a data however these cells has been  formatted.Unwanted formatting in cells also leads to increase of the file size. 
d. Formula's and links in the excel sheet also increases the size of the file. If formula and links are not required. Remove it and save the file which contains only text.
e. Reduce the size of graphics as they take lot of space in Excel, Remove unnecessary comments, cell shading, header footer, hyperlink as they take up additional space.

f.One more point contributed in the excel mania fb page: Save the files in .xlsb format. It will reduce the file size by 50% compared to .xlsx

Happy Learning- Excel Mania:)

EXCEL 2007-RIBBONS

EXCEL 2007-RIBBONS


Ribbons is the most important part of Microsoft Excel 2007. It contains the majority of commands.

Ribbons have replaced the 2003 menus and toolbars


Lets go through the different ribbons

                   1. Home Ribbons contains the frequently used 
              commands.

 

2. Insert Ribbon as the name suggest it is used to insert or add elements

 3.Page Layout Ribbon contains commands which controls the appearances of the worksheet

4.Formula Ribbon: contains commands dealing with the power aspects of MS Excel Functions/Formulas

5.Data Ribbons: We will find commands which will help us to import information to excel from access database, text file and web, filtering data, grouping and ungrouping etc

6.Review Ribbon:contain commands which deals with protecting and sharing the workbook, worksheets, adding comments etc

7.View Ribbon:containcommands in which the worksheet can be viewed while working. Commands to Zoom in and Zoom out, working with multiple worksheet simultaneously.

8:Developer Ribbon:contains commands to create macro, automation etc:

To make the Developer tab visible Click on in the excel--> Click on Excel Options-->Click on Popular-> Check Show developer tab in the Ribbon

This brings us to the end of this topic. Happy Learning.