Excel Magic Tricks 01 To 10 Finished 1s2r4m
This document was ed by and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this report form. Report 3i3n4
Overview 26281t
& View Excel Magic Tricks 01 To 10 Finished as PDF for free.
More details 6y5l6z
- Words: 1,442
- Pages: 10
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
B
1) 2) 3) 4) 5) 6)
D
E
Type numbers that can change into cells, not formulas Create formulas based on cell reference inputs, not typed in numbers Formulas must have "=" sign as first character in cell Relative cell references, and, absolute (locked) cell references Grab fill handle with cross-hair (angry rabbit) to copy formula SUM function keyboard shortcut is Alt + =.
Jan Revenue Expenses Net Income Budget Assumptions Ex as a % of Rev
45578601.xls
C
Net Income Budget Feb Mar 1000 1010 750 757.5 250 252.5
Total 1500 1125 375
3510 2632.5 877.5
0.75
T(1)
Page 1 of 10
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
B
1) 2) 3)
C
D
Highlight noncontiguous range (cells not next to each other) by holding the Ctrl Key Create a Chart by using the keyboard shortcut Alt + F1 Change Numbers in Assumption Table and formulas and charts update
Jan Revenue Expenses Net Income Budget
Net Income Budget Feb Mar 1,000.00 1,010.00 900.00 909.00 100.00 101.00
45578601.xls
0.9
Total 1,500.00 1,350.00 150.00
3,510.00 3,159.00 351.00
Net Income
Assumptions Ex as a % of Rev
E
200.00 150.00 100.00 50.00 0.00 Jan
T(2)
Feb
Mar
Page 2 of 10
A 1 2 3 4
B
1) 2) 3) 4)
5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Average ==>
45578601.xls
C
D
E
F
G
Ctrl + Enter to Populate all cells with the AVERAGE function Go To (F5), Special, Blanks Ctrl + Enter to put zero in all selected cells Notice that the Average function uses zeroes in the calculations, but not blanks Test 1
Test 2 55 18 0 47 5 63 13 34 39 30 0 70 0 16 27.86
Test 3 0 84 26 36 0 12 29 0 91 99 78 34 87 68 46
Test 4 0 83 49 19 7 41 38 57 77 23 0 55 82 7 38.43
T(3)
Test 5 15 26 0 29 0 76 47 0 95 83 31 0 76 45 37.36
Test 6 51 0 24 96 0 54 76 40 68 96 94 0 16 74 49.21
34 75 0 80 0 0 15 62 12 0 92 0 50 26 31.86
Page 3 of 10
A 1 1) 2 2) 3 3) 4 5 6 7 8 9 10
45578601.xls
B
C
D
E
F
Highlight range, type in light-colored cell, then use Ctrl + Enter top populate cells Alt + = is keyboard shortcut for auto SUM function Ctrl + Shift + 4 is keyboard shortcut for Currency Format $1.00 $1.00 $1.00 $1.00 $1.00 $5.00
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
T(4)
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
Page 4 of 10
A 1 2 3 4 5
C
D
E
F
G
H
I
1) 2) 3) 4) 5)
To name a range, select range, click in name box, type name, hit enter Name product name range Name VLOOKUP Table range Keyboard shortcut for Data Validation is Alt + D + L For Data Validation, Use List feature
6)
The VLOOKUP function takes the product name, looks in the first column of the VLOOKUP table, finds the name, looks in the second column of the VLOOKUP table, takes that price and then puts it in the price cell
6 7 8 9 10 11 12 13 14 15
B
Select Product to See Price Product 06
45578601.xls
Price
Product Product 01 Product 02 Product 03 Product 04 Product 05 Product 06
18.08
T(5)
Price 12.47 15.33 17.17 19.70 18.33 18.08
Page 5 of 10
A 1 2 3 4 5 6
1) 2) 3)
7 8
Sales
9 10 11 12 13 14 15 16 17 18
B
C
D
E
The keyboard shortcut for Conditional Formatting is Alt + O + D Add a formula that evaluates to True or False As you change the Sales or Hurdle number the conditional formatting will change
Hurdle =
200 Date $106.00 $56.00 $273.00 $55.00 $194.00 $80.00 $50.00 $75.00 $119.00 $0.25
SalesRep 3/2/2008 Chin 3/9/2008 Sheliadawn 2/25/2008 Isaac 2/26/2008 Sheliadawn 3/8/2008 Suix 2/27/2008 Al 2/28/2008 Chin 3/5/2008 Isaac 3/1/2008 Isaac 2/24/2008 Suix
Customer Google Google PCC PCC YouTube YouTube Yahoo Google Google Yahoo
Region MidWest North NorthWest MidWest West MidWest MidWest North NorthWest Al
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
45578601.xls
T(6)
Page 6 of 10
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
1) 2) 3) Sales
B
C
D
E
F
G
H
I
The keyboard short cut for removing format is Alt + E + A + F The keyboard short cut for removing content and format is Alt + E + A + A Ctrl + Z is Undo; Ctrl + Y is "Undo-Undo" (Redo) SalesRep 106 Chin 56 Sheliadawn 273 Isaac 55 Sheliadawn 194 Suix 80 Al 109 Chin 252 Isaac 119 Isaac 112 Suix
Customer Google Google PCC PCC YouTube YouTube Yahoo Google Google Yahoo
Region MidWest North NorthWest MidWest West MidWest MidWest North NorthWest Al
61 62 63 64 65
45578601.xls
T(7)
Page 7 of 10
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
B
C
D
E
F
G
H
I
1) 2)
Highlight range with Ctrl + Shift + Down Arrow Make Sure that there are blank columns after your data before you do "Text to Column"
3) 4)
Click the Text to Column button on the Data Ribbon, or in Excel 2003 use the Tools menu, Text to Column item In Step 2 tell it what character (space, comma, semi-colon) is between each word
Miki Isaac Tom Afrikan Tina Gigi Pham Miki Tina Chris Sue Sheliadawn Afrikan Isaac
45578601.xls
Ali Dino Sceana Ho Ward Smith Ho Smith Nano Mitchel Radcoolinator Ward Dino Smith
Afrikan Sue Tina Pham Franz Chris Chris Pham Bob Sue Pham Chris Tina Isaac
T(8)
Winney Mitchel Ho Birch East East Ali Ho Dino Mitchel Radcoolinator Dino Radcoolinator Smith
Page 8 of 10
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
B
C
D
E
F
G
1)
To cell content from different cells together, use the & (Ampersand) symbol
2)
The formula, =A5&" "&B5 , s the cell content from A5 and a blank space and the cell content from B5. This is an example of a Text formula
Tina Tom Bob Tina Rad Sue Afrikan Franz Sue Bob Miki Miki Isaac Gigi Tina Gigi
Ali Winney Smith Ward Dude Sceana Smith Nano Nano Bothell Nano Winney Sceana Mitchel Ali Nano
45578601.xls
Tina Ali Tom Winney Bob Smith Tina Ward Rad Dude Sue Sceana Afrikan Smith Franz Nano Sue Nano Bob Bothell Miki Nano Miki Winney Isaac Sceana Gigi Mitchel Tina Ali Gigi Nano
Tina Ali Tom Winney Bob Smith Tina Ward Rad Dude Sue Sceana Afrikan Smith Franz Nano Sue Nano Bob Bothell Miki Nano Miki Winney Isaac Sceana Gigi Mitchel Tina Ali Gigi Nano
T(9)
Page 9 of 10
A 1 2 3 4
1) 2)
C
D
E
F
G
H
To name a range, select range, click in name box, type name, hit enter The AVERAGE, MEDIAN, and MODE functions allow you to calculate different types of averages Different Types Of Averages
5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
B
Scores (Max = 50) 41 49 48 37 41 23 41 43 32 21 22 40 32 45 41 34 30
45578601.xls
Mean = Add Up and Divide by the Count
Median = the one in the Mode = the one that middle occurs most frequently 36.47 40 41
T(10)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
21 22 23 30 32 32 34 37 40 41 41 41 41 43 45 48 49
Page 10 of 10
B
1) 2) 3) 4) 5) 6)
D
E
Type numbers that can change into cells, not formulas Create formulas based on cell reference inputs, not typed in numbers Formulas must have "=" sign as first character in cell Relative cell references, and, absolute (locked) cell references Grab fill handle with cross-hair (angry rabbit) to copy formula SUM function keyboard shortcut is Alt + =.
Jan Revenue Expenses Net Income Budget Assumptions Ex as a % of Rev
45578601.xls
C
Net Income Budget Feb Mar 1000 1010 750 757.5 250 252.5
Total 1500 1125 375
3510 2632.5 877.5
0.75
T(1)
Page 1 of 10
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
B
1) 2) 3)
C
D
Highlight noncontiguous range (cells not next to each other) by holding the Ctrl Key Create a Chart by using the keyboard shortcut Alt + F1 Change Numbers in Assumption Table and formulas and charts update
Jan Revenue Expenses Net Income Budget
Net Income Budget Feb Mar 1,000.00 1,010.00 900.00 909.00 100.00 101.00
45578601.xls
0.9
Total 1,500.00 1,350.00 150.00
3,510.00 3,159.00 351.00
Net Income
Assumptions Ex as a % of Rev
E
200.00 150.00 100.00 50.00 0.00 Jan
T(2)
Feb
Mar
Page 2 of 10
A 1 2 3 4
B
1) 2) 3) 4)
5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Average ==>
45578601.xls
C
D
E
F
G
Ctrl + Enter to Populate all cells with the AVERAGE function Go To (F5), Special, Blanks Ctrl + Enter to put zero in all selected cells Notice that the Average function uses zeroes in the calculations, but not blanks Test 1
Test 2 55 18 0 47 5 63 13 34 39 30 0 70 0 16 27.86
Test 3 0 84 26 36 0 12 29 0 91 99 78 34 87 68 46
Test 4 0 83 49 19 7 41 38 57 77 23 0 55 82 7 38.43
T(3)
Test 5 15 26 0 29 0 76 47 0 95 83 31 0 76 45 37.36
Test 6 51 0 24 96 0 54 76 40 68 96 94 0 16 74 49.21
34 75 0 80 0 0 15 62 12 0 92 0 50 26 31.86
Page 3 of 10
A 1 1) 2 2) 3 3) 4 5 6 7 8 9 10
45578601.xls
B
C
D
E
F
Highlight range, type in light-colored cell, then use Ctrl + Enter top populate cells Alt + = is keyboard shortcut for auto SUM function Ctrl + Shift + 4 is keyboard shortcut for Currency Format $1.00 $1.00 $1.00 $1.00 $1.00 $5.00
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
T(4)
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
$1.00 $1.00 $1.00 $1.00 $1.00 $5.00
Page 4 of 10
A 1 2 3 4 5
C
D
E
F
G
H
I
1) 2) 3) 4) 5)
To name a range, select range, click in name box, type name, hit enter Name product name range Name VLOOKUP Table range Keyboard shortcut for Data Validation is Alt + D + L For Data Validation, Use List feature
6)
The VLOOKUP function takes the product name, looks in the first column of the VLOOKUP table, finds the name, looks in the second column of the VLOOKUP table, takes that price and then puts it in the price cell
6 7 8 9 10 11 12 13 14 15
B
Select Product to See Price Product 06
45578601.xls
Price
Product Product 01 Product 02 Product 03 Product 04 Product 05 Product 06
18.08
T(5)
Price 12.47 15.33 17.17 19.70 18.33 18.08
Page 5 of 10
A 1 2 3 4 5 6
1) 2) 3)
7 8
Sales
9 10 11 12 13 14 15 16 17 18
B
C
D
E
The keyboard shortcut for Conditional Formatting is Alt + O + D Add a formula that evaluates to True or False As you change the Sales or Hurdle number the conditional formatting will change
Hurdle =
200 Date $106.00 $56.00 $273.00 $55.00 $194.00 $80.00 $50.00 $75.00 $119.00 $0.25
SalesRep 3/2/2008 Chin 3/9/2008 Sheliadawn 2/25/2008 Isaac 2/26/2008 Sheliadawn 3/8/2008 Suix 2/27/2008 Al 2/28/2008 Chin 3/5/2008 Isaac 3/1/2008 Isaac 2/24/2008 Suix
Customer Google Google PCC PCC YouTube YouTube Yahoo Google Google Yahoo
Region MidWest North NorthWest MidWest West MidWest MidWest North NorthWest Al
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
45578601.xls
T(6)
Page 6 of 10
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
1) 2) 3) Sales
B
C
D
E
F
G
H
I
The keyboard short cut for removing format is Alt + E + A + F The keyboard short cut for removing content and format is Alt + E + A + A Ctrl + Z is Undo; Ctrl + Y is "Undo-Undo" (Redo) SalesRep 106 Chin 56 Sheliadawn 273 Isaac 55 Sheliadawn 194 Suix 80 Al 109 Chin 252 Isaac 119 Isaac 112 Suix
Customer Google Google PCC PCC YouTube YouTube Yahoo Google Google Yahoo
Region MidWest North NorthWest MidWest West MidWest MidWest North NorthWest Al
61 62 63 64 65
45578601.xls
T(7)
Page 7 of 10
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
B
C
D
E
F
G
H
I
1) 2)
Highlight range with Ctrl + Shift + Down Arrow Make Sure that there are blank columns after your data before you do "Text to Column"
3) 4)
Click the Text to Column button on the Data Ribbon, or in Excel 2003 use the Tools menu, Text to Column item In Step 2 tell it what character (space, comma, semi-colon) is between each word
Miki Isaac Tom Afrikan Tina Gigi Pham Miki Tina Chris Sue Sheliadawn Afrikan Isaac
45578601.xls
Ali Dino Sceana Ho Ward Smith Ho Smith Nano Mitchel Radcoolinator Ward Dino Smith
Afrikan Sue Tina Pham Franz Chris Chris Pham Bob Sue Pham Chris Tina Isaac
T(8)
Winney Mitchel Ho Birch East East Ali Ho Dino Mitchel Radcoolinator Dino Radcoolinator Smith
Page 8 of 10
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
B
C
D
E
F
G
1)
To cell content from different cells together, use the & (Ampersand) symbol
2)
The formula, =A5&" "&B5 , s the cell content from A5 and a blank space and the cell content from B5. This is an example of a Text formula
Tina Tom Bob Tina Rad Sue Afrikan Franz Sue Bob Miki Miki Isaac Gigi Tina Gigi
Ali Winney Smith Ward Dude Sceana Smith Nano Nano Bothell Nano Winney Sceana Mitchel Ali Nano
45578601.xls
Tina Ali Tom Winney Bob Smith Tina Ward Rad Dude Sue Sceana Afrikan Smith Franz Nano Sue Nano Bob Bothell Miki Nano Miki Winney Isaac Sceana Gigi Mitchel Tina Ali Gigi Nano
Tina Ali Tom Winney Bob Smith Tina Ward Rad Dude Sue Sceana Afrikan Smith Franz Nano Sue Nano Bob Bothell Miki Nano Miki Winney Isaac Sceana Gigi Mitchel Tina Ali Gigi Nano
T(9)
Page 9 of 10
A 1 2 3 4
1) 2)
C
D
E
F
G
H
To name a range, select range, click in name box, type name, hit enter The AVERAGE, MEDIAN, and MODE functions allow you to calculate different types of averages Different Types Of Averages
5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
B
Scores (Max = 50) 41 49 48 37 41 23 41 43 32 21 22 40 32 45 41 34 30
45578601.xls
Mean = Add Up and Divide by the Count
Median = the one in the Mode = the one that middle occurs most frequently 36.47 40 41
T(10)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
21 22 23 30 32 32 34 37 40 41 41 41 41 43 45 48 49
Page 10 of 10
Related Documents 3h463d
Excel Magic Tricks 01 To 10 Finished 1s2r4m
November 2022 0
Excel Magic Tricks To Start n3v10
October 2021 0
Magic Tricks To Amaze Yourself 5s651b
November 2020 0
October 2021 0
Contents - Magic Tricks 2f3q2z
April 2020 45