5 Excel Data5 Excel Data - Excel TipsNTricks

5 Excel Data

Tip 5.1 : Guidelines to

Tip 5.1 : Guidelines to consider before sorting data in Microsoft Excelconsider before sorting data in Microsoft Excel

•

• SelectingSelecting cellcell

Do not select a column or

Do not select a column or datadata range in arange in a datadata list on a sheet. Select only alist on a sheet. Select only a

single cell. Click the

single cell. Click the SortSort icon to automatically sort the entire list. Theicon to automatically sort the entire list. Thedatadata

will be sorted according to the field of the

will be sorted according to the field of the cell selected.cell selected.

•

• FormulasFormulas

Be careful when

Be careful when sorting datasorting data if there areif there areformulasformulas in the cells.in the cells. SortingSorting data

data when cells are linked to other rows or when cells are linked to other rows or cells in other sheets could distortcells in other sheets could distort

the calculations. Be meticulous when

the calculations. Be meticulous whensortingsorting a list witha list with formulasformulas that havethat have

Names (which are defined with absolute references by

Names (which are defined with absolute references by default) or withdefault) or with

formulas

formulas that have absolute references.that have absolute references.

•

• Insert a sequence column to restore original Insert a sequence column to restore original orderorder

Insert an additional column into the

Insert an additional column into the datadata list with list with ascending numbersascending numbers,,

1,2,3,…(do not use a

1,2,3,…(do not use a formula) beforeformula) beforesortingsorting the data. If a list the data. If a list includes aincludes a

column with consecutive dates, use this column as the first

column with consecutive dates, use this column as the firstsortingsortingcolumncolumn

Tip 5.2 : Change the auto save default file location in Microsoft Excel

Tip 5.2 : Change the auto save default file location in Microsoft Excel From the

From the ToolsTools menu, selectmenu, select Options, GeneralOptions, General tab.tab.

A new Excel file is

A new Excel file is automatically saved for the firstautomatically saved for the firsttimetime (if you have not specified a(if you have not specified a

location on the hard disk) in the

location on the hard disk) in the My DocumentsMy Documents folder on the C: drive.folder on the C: drive.

Change the default location as

Change the default location as desireddesired

Tip 5.3 : Using the COUNTIF function to filter a list into a unique record in

Microsoft Excel

Microsoft Excel 1.

1. In Cell B1(see screen shot), enter theIn Cell B1(see screen shot), enter the texttext Unique Record.Unique Record.

2. Select Cell Select Cell B2, and B2, and enter the enter the formula =IF(COUNTIFformula =IF(COUNTIF($A$2:A2,A2)>1,1($A$2:A2,A2)>1,1,0).,0).

3. Copy the formula from Cell B2 to Cell Copy the formula from Cell B2 to Cell B40 (the customer list in Column AB40 (the customer list in Column A

extends through Cell A40).

4. From theFrom the DataData menu, selectmenu, select FilterFilter..

5. Open the filtering drop-down list in Cell B1 by clicking the arrow, and select 0.Open the filtering drop-down list in Cell B1 by clicking the arrow, and select 0.

Explanation: Explanation:

The

The COUNTIFCOUNTIF function counts the number of cells within a range that meet thefunction counts the number of cells within a range that meet the

given criteria.

For example, the

For example, the COUNTIFCOUNTIF function returns the number of function returns the number of times a customertimes a customer

appears in a list. The IF function uses the

appears in a list. The IF function uses the results of theresults of theCOUNTIFCOUNTIF calculation. If thecalculation. If the

result of the calculation is greater than 1, the

result of the calculation is greater than 1, the result of calculating the formula is 1. If result of calculating the formula is 1. If

it is not, the

it is not, the result is 0. Because the range runs from an absolute cell ($A$2) to result is 0. Because the range runs from an absolute cell ($A$2) to aa

relative cell (A2), the cell range checked by the

relative cell (A2), the cell range checked by theCOUNTIFCOUNTIF functions changes whenfunctions changes when

the formula is copied.

With

Tip 5.4 : Consolidate with formulas if the row & column headers of all

worksheets are identical in Microsoft Excel

worksheets are identical in Microsoft Excel If you have several worksheets and every

If you have several worksheets and everyworksheetworksheet has an identical structure -has an identical structure -

that is, the headings in row 1

that is, the headings in row 1 and column A are identical from sheet to sheet, youand column A are identical from sheet to sheet, you

can create a total

can create a total worksheetworksheet using simpleusing simple formulasformulas..

Example: Example:

In the workbook 3

In the workbook 3 sheets - January, February and March contain tables with salarysheets - January, February and March contain tables with salary

data.

On every sheet, the items in cells

On every sheet, the items in cells A2:A8 are identical.A2:A8 are identical.

On every sheet, the employees listed in B1:H1 are identical and never change.

Add a new worksheet, change the

Add a new worksheet, change the worksheetworksheet name to Total. Use a formula like thename to Total. Use a formula like the

one shown in cell B2 (

one shown in cell B2 ( see screen shot ) to add the other sheets.see screen shot ) to add the other sheets.

The formula in Cell B2 is

The formula in Cell B2 is =SUM (January:March!B2)=SUM (January:March!B2)..

The technique for entering a formula to sum a single cell on

The technique for entering a formula to sum a single cell on a number of sheets.a number of sheets.

1. Select Cell B2 in the sheetSelect Cell B2 in the sheetnamename Total.Total.

2. In the cell, typeIn the cell, type =SUM=SUM, and press, and press Ctrl+ACtrl+A (the shortcut for displaying the(the shortcut for displaying the

formula argument box).

3. In the first argument box, select theIn the first argument box, select the namename of the first sheet in of the first sheet in the rangethe range

January.

4. PressPress ShiftShift, and select the last sheet , and select the last sheet in the range March.in the range March.

In document Excel TipsNTricks (Page 31-34)