Excel Tips
Read these 56 Excel Tips tips to make your life smarter, better, faster and wiser. Each tip is approved by our Editors and created by expert writers so great we call them Gurus. LifeTips is the place to go when you need to know about Microsoft tips and hundreds of other topics.
Automatically Insert Numbers
To automatically insert a series of numbers, type in the first number and fill down, click Edit, Fill and Series.
AutoFormat
The AutoFormat command applies predefined styles to worksheets or selected ranges of cells.
Formatting Shortcuts
The shortcuts for formatting are:
CTRL + B = Bold
CTRL + I = Italicize
CTRL + U = Underline
CTRL + SHIFT + P = Change font size
CTRL + SHIFT + F = Change font
Freeze Panes
To compare the first column with the last column in the spreadsheet, highlight the first column. Then go to Window and select Freeze Panes. Scroll right at the bottom, all columns will move except for the first one.
Copying, Pasting, Changing, Deleting (Cutting), or Finding Shortcuts
The shortcuts for copying, pasting, changing, deleting (cutting), or finding are:
CTRL + Z = Undo last action
CTRL + Y = Repeats last action
CTRL + X = Cuts
CTRL + C = Copy
CTRL + V = Pastes
CTRL + SHIFT + " = Pastes formula from cell above into current cell
CTRL + F = Finds a word or phrase
CTRL + G = Replace a word with another word
Border or Color
To add a border or color to the Worksheet, select the cells and click on Format, Cells and Border option. For color, click on Patterns tab.
Regular Text
After you format a Worksheet and then want to go back to regular text, click Edit, Clear and Formats option.
Automatically Enter the Months
To enter the months from January to December, type January and position the cursor over the right hand bottom corner of the active cell. The cursor should change into a cross, drag the cross down/across will automatically enter the rest of the months in the cells below/side.
Absolute Address
An absolute address in a formula refers to a specific cell location or range. For example, $A$1 always refers to cell A1.
3-D Reference
A 3-D reference links worksheets together. When the name of the sheet tab appears in a formula followed by an exclamation point, the cell address is a 3-D reference.
Protection Password
Protection Passwords give you additional control over who can access locked cells.
Parentheses in Formulas
Excel performs calculations inside parentheses before other operations in a formula.
Fit To Page
When you activate Fit To Page, Excel reduces or enlarges the worksheet to fill the number of pages you specify.
Conditional Format
For certain values to stand out in a Worksheet, highlight the Worksheet and click on Format and Conditional Format option that allows to set conditions. If a number meets those conditions, it will be formatted differently to the other numbers.
Decimal Places
To format numbers that show two decimal places, select the cells and click on the Increase Decimal icon in the formatting toolbar.
Comparison Operators
Comparison operators are symbols used to specify criteria in searches and in formulas. The most common operators are, equals, greater than, less than, greater than or equal to, less than or equal to, not equal to.
Arithmetic Operators
Arithmetic operators are the symbols used in formulas to calculate values, like as addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^).
Average or Standard Deviation
To find the average or standard deviation of a set of numbers, click on Insert and Function and the function
Click-Entry Method
The click-entry method is another way you can use to create a formula. You can click cells to enter cell references instead of typing them.
AutoCalculate
To perform a function without inserting it into a worksheet, use the AutoCalculate on the status bar. Then select the function you want to perform, and the range you want to perform it on.
Cell Comments
Cell comments are hidden text notations that can be added to any cell. To indicate that a cell contains cell comments, Excel places a red square in the upper right corner of the cell.
Protection
You can lock selected parts of a worksheet to protect those parts from being altered by an outside source by activating the Protection feature under the Tools menu.
Change the Format of Numbers
To change the format of numbers in the cells, select the cells and click Format, Cells and Number tab and choose the format.
Print Part of the Worksheet
To print part of a worksheet, select the part to print and go to File, Print Area and Set Print Area.
Relative Addresses
A relative address is a standard cell reference that appears like this: A1,E15,M42. A relative address changes if you copy a formula that contains it to a new location on the worksheet.
Open, Close, Save, and Print Shortcuts
The shortcuts for opening, closing, saving, and printing are:
CTRL + N = Opens new document
CTRL + W = Closes document
CTRL + O = Opens saved document
CTRL + S = Saves document
F12 = Shows “Save As” box
CTRL + P = Prints the document
AutoFilter
AutoFilter mode lets you display only the rows in a list of data that meet the criteria that you specify.
Moving and Selecting Shortcuts
The shortcuts for moving and selecting are:
TAB = Left to right
SHIFT + TAB = Right to left
ENTER = Down
SHIFT + ENTER = Up
CTRL + PGUP = To cell a1
CTRL + PGDN = End of data
CTRL + RIGHT ARROW = End of row
CTRL + LEFT ARROW = Beginning of row
CTRL + DOWN ARROW = End of column
CTRL + UP ARROW = Beginning of column
Mixed Addresses
Mixed addresses are cell references that combine absolute and relative addressing, making either the row or the column absolute. Examples of mixed addresses: $A1,E$15,$C3.
Highlight Cells
To highlight cells from the current cell to another cell, press F5, then type in the second cell coordinates and hit Shift and Enter.
Exclamation Point
An exclamation point separates the sheet reference from the cell reference in formulas using 3-D references.
Order Of Precedence
The order of precedence is the order in which Excel calculates a formula.
Colon Symbol
A colon symbol inserted between two cell references in a formula defines a range of adjacent cells.
Column Length
If the columns are too small for the data in them, change the column length by going up to the seam at the top of the Worksheet and double click on the mouse. When the cursor changes into a two headed arrow or when the cursor changes to the two headed arrow, drag with the mouse to make the column wider.
Insert a Chart
To insert a chart, select the cells to draw the chart and then click on the Chart icon at the top of your screen, or go to Insert and select Chart.
PivotTable
PivotTables are interactive worksheet tables that allow you to summarize data with great flexibility.
Protect Worksheet
To protect a worksheet or workbook, click on Tools and Protect option. This will prevent people from making changes to to the Worksheet.
If Function
An If function performs a logical test on an argument, then performs an action based on whether the logical test is true or false.
| Guru Spotlight |
| Ray Lokar |
