The Excel workbook used in class to demonstrate these quick tips can be downloaded instantly at the following address: www.ExcelAdvisor.net Password: 2007collins
1
|
Right Click Status Bar - (View sums, averages, mins, maxes immediately)
|
2
|
CTRL + Mouse Scroll - (Zoom in & out with your mouse)
|
3
|
Double Click the Format Painter - (Tool sticks until clicked again)
|
4
|
Replace Formatting - (Find and replace one formatting with another)
|
5
|
Click on Edge of Cell - (Navigate in a range of cells)
|
6
|
Turn off Task Pane - (Put an end to TaskPane)
|
7
|
Control Tilde (CTRL + ~) - (View underlying formulas)
|
8
|
Indent Icon - (Indent cells or columns instantly)
|
9
|
ALT + Down Arrow (or Shift-F10) - (Pick from a drop down list)
|
10
|
F4 - (Repeat the last command such as insert rows or change row height)
|
11
|
Alt + Enter - (Wrap text instantly)
|
12
|
& - (Combine text from multiple cells)
|
13
|
Right Click Tab, Copy, Create Copy - (Insert new sheet with headers, footers, etc)
|
14
|
File, Send To, Mail Recipient - (E-Mail a worksheet, workbook or chart)
|
15
|
ComboBox from Forms Toolbar - (Insert a combobox)
|
16
|
Double Click Fill Handle - (Copies formula down the relevant range)
|
17
|
=Upper, =Lower, =Proper - (Change text case)
|
18
|
Paste Special, Values - (Convert formulas to numbers)
|
19
|
F4 in Edit Mode - (Toggle Absolute References)
|
20
|
Paste Special, Transpose - (Invert a matrix of numbers)
|
21
|
Ctrl + D - (Copy Data to the down)
|
22
|
Ctrl + R - (Copy data to the right)
|
23
|
Defined Names - (Refer to names rather than cell addresses)
|
24
|
Data, AutoFilter, Advanced, Copy, Unique - (Extract unique values)
|
25
|
Tools, Options, Calculation, Precision as Displayed (Avoid rounding errors)
|
26
|
Right Click Toolbar, Options, Show Full Menus - (Show all menu options)
|
27
|
Tools, View, Zero Values - (Hide zero values)
|
28
|
Tools, AutoCorrect, Smart Tags, None - (Turn off Smart Tags)
|
29
|
Filter Data, Apply Color, Un-filter Data - (Color filtered results)
|
30
|
PDF2XL - ($95 product converts PDF's to Excel files)
|
31
|
Data Validation - (Insert a pop up comment into a cell)
|
32
|
Ctrl+Shft+End - (Select row to the right)
|
33
|
Ctrl+Shft+Home - (Select row to the left)
|
34
|
F11 - (Produce a quick chart)
|
35
|
Print Area in Name Box - (Quickly identify the print area)
|
36
|
Displaying the Styles Tool - (Toolbar access to styles)
|
37
|
Format, Styles - (Create new styles)
|
38
|
Control Panel, Regional Options - (Control how dates are displayed)
|
39
|
=Substitute - (Remove or replace unwanted characters)
|
40
|
View, Sized with Window - (Resizes chart to fit Window)
|
41
|
Ctrl+Spacebar - (Select a column)
|
42
|
Shift+Spacebar - (Select a row)
|
43
|
Alt+Tab - (Toggle between applications or Excel workbooks)
|
44
|
Delete Blank Rows and Columns, Ctrl+S - (Reduce relevant area and scroll bar)
|
45
|
Copy Formula, to Blank Cells - (Fill in missing data in a list)
|
46
|
=ISTEXT(A1) - (Use this formula in conditional formatting to format text only)
|
47
|
Replace, Within - (Tip for replacing throughout entire workbook)
|
48
|
=LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")) - (Count the occurrence of a character)
|
49
|
=MID(A25,FIND("*",SUBSTITUTE(A25,"\","*",LEN(A25)-LEN(SUBSTITUTE(A25,"\",""))))+1,LEN(A25)) - (Extract the Filename)
|
50
|
=SumIF - (Sum only those numbers that meet specific criteria)
|
