Original subtitles

1

Here's my favorite list of shortcuts when designing reports or dashboards in Excel.

2

I use this often when I work in Excel especially when I'm designing reports.

3

Let me quickly take you through them.

4

CTRL+SHIFT+C and CTRL+SHIFT+V

5

are great shortcuts not just for Excel but I use them often in Powerpoint as well and they copy the object

6

attributes.

7

For example, if you have an object inserted here... Let's put in a rectangle.

8

Just going to copy and paste it two times and change the formatting of the last one.

9

Let's add a text.

10

Make it red and change the color of this to gray and no shape outline. The text of these is white

11

so one of them has text, one of them doesn't have text.

12

If I would copy and paste this, I'm going to copy that whole object and paste it. But I just want to copy

13

the attributes of this.

14

I'm going to click on it and do CTRL+SHIFT+C and click on this and then press CTRL if you want

15

to select more objects and highlight the other object and do CTRL+SHIFT+V.

16

That works great in Powerpoint as well.

17

To select all objects, you can press CTRL+A

18

but you need to click on an object first.

19

If you're just in a cell and you do CTRL+A,

20

it highlights everything.

21

If you want to highlight all your objects, click on an object and then press CTRL+A and it selects

22

all your objects. One way that you can also get a list of all the objects that's used,

23

you can go to Find & Select > Selection pane

24

and here, you have the full list.

25

These must be the rectangles that I just added.

26

If I click on Hide,

27

You can see they disappear.

28

I can unhide them here.

29

If you have a lot of objects and it's difficult to click on them, you go to here and then highlight them

30

here, press CTRL and highlight more

31

from here. It's sometimes easier to work here than directly on the objects in the spreadsheet especially

32

if you have a few on top of each other.

33

I can also highlight them from here and press Delete. This one

34

I use a lot. It's to format cells.

35

You can get to it either by right mouse clicking and going to Format Cells or by just pressing CTRL+1

36

and going to the Format Cells. F4 does a lot of things. One of the things it does is to repeat

37

the last formatting action.

38

Let's say you have 333 here. One formatting I'm going to do with this with CTRL+1

39

is to add decimal places to this

40

and then what I'm going to do is to make it this color.

41

If I go to these cells and press F4 and F4 and F4, it puts the color.

42

Let's take a look if it puts the formatting of the number.

43

It doesn't do that.

44

It just repeats the last formatting action and I use that quite often when I finished a table design

45

and then I add borders to that table with specific colors.

46

I just go to the other cells and press F4 and it just copies the borders but leaves the rest of the

47

formatting alone.

48

The other useful one is to copy formulas without formatting using F2 and CTRL+ENTER.

49

Let's say here, I have a formula.

50

It equals this number.

51

I'm going to fix it with F4.

52

This is another great shortcut. The F4 key fully fixes your cell references. For instance, if you click

53

it again,

54

it semi-fixes just a row

55

and if you click it again, it fixes just the column and then it leaves everything relative.

56

and then once again, you click it, it has the double dollar signs in there.

57

I'm going to say equals this plus row E1 which is going to return 1

58

but when I drag it down, it's going to return 2 and 3.

59

This is a formula that we're going to cover later on in the Formulas toolbox.

60

Let's say I would drag this down.

61

.

62

It copies the formatting of the first cell along with it.

63

What if I didn't want that? What I have to do is to highlight the area that I want the formula to be

64

copied to.

65

Then, click F2. You see, it includes the first cell that has my formula and then press CTRL+ENTER.

66

That copies the formula without the formatting.

67

Another thing that bothered me for a while is drawing a straight line from shapes. If you try that,

68

you can see that it can be a challenge sometimes when you go like this to get it really straight.

69

And it's not just in Excel but in Word and PowerPoint as well.

70

So one way around this is to go to Insert, to go to your line, click on it, but before you start drawing it, hold

71

down the SHIFT key and then draw it.

72

And that forces the line to be straight.

73

Another one is to insert bullet points in text.

74

You can insert bullet points by going to Insert > Symbol or finding the bullet points symbol and inserting

75

it in your formula or in your text.

76

But you can also use the ALT+7 shortcut key.

77

I put a note here that it only works on a larger keyboard that has a numeric keypad.

78

If you're using a laptop, you need to enable Number Lock first.

79

But some laptops just don't have that functionality. The laptop I'm using right now to film this doesn't

80

have this functionality so I can't show it to you. But try it on your desktop computer or if you have a

81

keyboard, try that. It will insert bullet points in text.

82

One other one that can be a bit frustrating is trying to move charts with the arrow keys.

83

Let's go here and let's just insert a mini chart.

84

If you just try to move this with the arrow keys, this is what happens.

85

It moves through the different chart elements but it doesn't actually move your chart. The way to move

86

it is

87

you have to press down CTRL and then click on the edges of your chart to get these circles and now,

88

you can move it with the arrow keys.

89

Another way that you can make sure that you're moving it in a straight fashion is when you're moving this with

90

the mouse keys,

91

hold down the SHIFT key and then it locks it into the rows and the column cell. You can see, it moves it

92

straight over

93

then it moves this straight down over. These two can come in handy when you work with charts.

94

Another one is the ALT+ENTER key that inserts a paragraph inside a cell.

95

Let's say here, I wanted to insert a paragraph. When I press enter,

96

it leaves the formula bar. To make it insert a paragraph inside the cell,

97

I need to press the ALT and the ENTER key. Here are some basic ones for navigation.

98

Let's go back to here.

99

If you want to jump to the end to the last cell in your table, press CTRL and then the down arrow

100

key. Press CTRL then

101

up arrow key to jump up. If you want to highlight everything on the way, press CTRL+SHIFT+↓ and

102

then press CTRL+SHIFT and the right arrow key.

103

and then you highlight your whole table. CTRL+SHIFT+↑,

104

you go back up. If you want to move through your tabs, you can press CTRL and then PAGE UP and CTRL,

105

PAGE DOWN.

106

F2 is a great one when you want to edit your formula. If you want to edit your formula right now,

107

you press the F2 key and it edits it in the cell. If you don't want to make changes to your formula,

108

you can press ESC to leave your formula.

109

I already covered the F4 key that allows you to change the cell references between absolute and relative

110

cell references.

111

That's the one that puts the dollar sign (this one) on your cell references.

112

Another good one that can come in handy when you write larger formulas and you want to debug and

113

track your formula is the F9 key.

114

If I go to one of these where I have a formula, you can highlight parts of your formula and then press

115

the F9 key. It actually translates it to what is in those references.

116

Press CTRL+Z to go back or ESC to leave.

117

In this case, it says saying this sumifs plus this sumifs, I can highlight this and then press

118

the F9 key and I know

119

this is the number is the result of the second sumifs and then press ESC to leave or CTRL+Z

120

to go back

121

because if you press enter, it keeps that number in your formula.

122

CTRL+Z I just covered is to go back.

123

One that I missed out on for quite a while was the double click Format Painter. You probably know

124

the Format Painter that if you click on a cell, click on the formatting, it copies that formatting to

125

another cell.

126

But, if you want to reuse that formatting on different cells, all you have to do is double click really

127

quickly on the Format Painter icon and then you can reuse that on other cells. When you're done, you can

128

just press ESC to leave. The other one that can come in really handy is the Clipboard.

129

I have it in CTRL+C+C so if you press C twice but it might not work for you for the first time,

130

you just have to activate an option.

131

This is the Clipboard. I'm just going to open my Clipboard.

132

Anything you copy gets added to the clipboard. If I go in this cell and press CTRL+C, it's added

133

to my Clipboard.

134

Let's say I also want this number.

135

I'm going to do CTRL+C and I might even want a formula.

136

I'm going to do CTRL+C. They are all added to my Clipboard and I can reuse them when I'm writing a formula.

137

I could say it equals this plus this.

138

There is an option to make CTRL+C+C work and that's this option.

139

Make sure you have a tick mark where it says "Show Office Clipboard when Ctrl+C Pressed Twice"

140

You can see you can toggle it on and off.

141

If I close this and clear it all, whenever I press CTRL+C+C, it opens my Clipboard.

142

That's it! These are the list of my favorite shortcuts in Excel.

143

I suggest you pick a few of these shortcut keys and use them immediately in your files. You can also

144

print the list out from the demo workbook and keep it handy.

145

On the one side, we have the shortcut keys to help us be faster and in the next lecture, I'm going to

146

show you some additional tools that you should be using

147

if you use Excel on a frequent basis.