M.S. Excel Quick Tips

M.S. Excel Quick Tips

How to Change default font in Excel

 >>>Excel Options >>Popular >> Use this font >> OK.

How to Automatically correct Capitalization

>>>Excel Options >> Proofing >> Auto Correct Options >> OK.

How to Show Days for Dates
>>> =TEXT(A1,"dddd") for day, TEXT(A1,"mmmm") for Month, 
A1 is the cell data.

How to round the sum totals

=SUM(ROUND(B2:B6)) > CTRL + SHIFT + ENTER
Look at formula bar for the result 

Insert Alternative Rows

Insert alternatively rows in existing cell data
> insert a new column at left at existing column data

> write 1 in a of new column and fill series down to end of data
copy this data and paste again in the end of data.

> select all data > from data tab, click on sort, add level, sort from a then b

>now finally delete the column a, ok.

Conditional Formating in Excel 2007, 2010

Conditional Formating in Excel 2007, 2010

Highlight column data in two separate colours

> select the data

> From Home tab > conditional Formating

> Manage Rules

> New Rule > Format cell that contain

> cell value Greater Than (x) Format Fill color Red

> New Rule > Format cell that contain

> cell value Less Than (x) Format Fill color Blue

How to Sort in Excel 2007, 2010

Sorting

Go to data tab > Sort > Add Level > sort by column A > then by Column B > ok



How to filter specific cell data

How to filter specific cell data and copy entire rows.
Office 2007, 2010
This is my original sheet

I like to copy entire rows having value 0.00 in column B
1- select column B
2- click on data tab
3- click on filter

4- Click on arrow (circled) in column B top
5- From list select 0.00, ok
6- Now copy the data and paste in new sheet
Ok

Delete Empty Rows Quickly

Delete Empty Rows Quickly
1- select the first column from top to bottom,
2- Press F5
3-Click special
4- check the blank, ok

5-right click, delete, delete entire row, ok

How to create scatter chart in MS Excel

Select the data
go to insert
scatter,here are more options
 1-lines with marker,
 2-only line, select required,
 3-only markers
right click on the chart,
move the chart to new sheet, if required.
right click on RD, below chart
Format axis

How to Add a Data Table to an Excel 2007 Chart

In Excel 2007, data tables display chart values in a grid beneath the chart.

Layout, Show data table, show with legends or without legengs.

Right click on the tabe, select data, add, series name, select data range, ok.

Excel Navigation

Navigate cells Complete actions quickly 

Up, down, left, right	• , �, �, •
Right	Tab
Start of worksheet	Ctrl + Home
End of worksheet	Ctrl + End
Start of row	Home
End of row	End, Enter
Up/down one screen	Page Up/ Down

Select

Range of cells	Shift + move1
Column	Ctrl + space
Row	Shift + space
Entire worksheet	Ctrl + A

 Work in a range

Select current range	Ctrl + *
Move to range border	Ctrl + • , �, �, •
Move up	Enter
Move down	Shift + Enter
Move right	Tab
Move left	Shift + Tab
Jump to corner	Ctrl + . (period)
Enter data/formula in	Ctrl + Enter
all selected cells

 Standard actions Format characters

Save	Ctrl + S
Print	Ctrl + P
New workbook	Ctrl + N
Open	Ctrl + O
Copy	Ctrl + C
Cut	Ctrl + X
Paste	Enter, or Ctrl + V

Repeat last action	F4
Today’s date	Ctrl + ; (semi-colon)
Line break in cell	Alt + Enter

Bold	Ctrl + B
Italic	Ctrl + I
Underline	Ctrl + U

 Navigate workbooks

Go to next workbook	Ctrl + F6
Previous Worksheet	Ctrl + Page Up
Next Worksheet	Ctrl + Page Down

 Work with dialog boxes

OK (or highlighted button)	Enter
Cancel	Esc
Move to next option	Tab
Switch focus to worksheet	F6

 Copy data from adjoining cells

Fill down	Ctrl + D
Fill right	Ctrl + R
Copy formula from above	Ctrl + '
Copy value from above	Ctrl + "

Custom Number Formats

1                    Select cells to be rounded

2                    Choose Format�Cells�Number tab

3                    Select Custom Category

4                    In the Type box, type a format, e.g.:

Format	Number	Becomes
#,	54,223	54
“XY”000	123	XY123