How to create a 1,000 x 1,000 multiplication table in 5 minutes or less

TediSoriano 83 views 215 slides Nov 30, 2017
Slide 1
Slide 1 of 215
Slide 1
1
Slide 2
2
Slide 3
3
Slide 4
4
Slide 5
5
Slide 6
6
Slide 7
7
Slide 8
8
Slide 9
9
Slide 10
10
Slide 11
11
Slide 12
12
Slide 13
13
Slide 14
14
Slide 15
15
Slide 16
16
Slide 17
17
Slide 18
18
Slide 19
19
Slide 20
20
Slide 21
21
Slide 22
22
Slide 23
23
Slide 24
24
Slide 25
25
Slide 26
26
Slide 27
27
Slide 28
28
Slide 29
29
Slide 30
30
Slide 31
31
Slide 32
32
Slide 33
33
Slide 34
34
Slide 35
35
Slide 36
36
Slide 37
37
Slide 38
38
Slide 39
39
Slide 40
40
Slide 41
41
Slide 42
42
Slide 43
43
Slide 44
44
Slide 45
45
Slide 46
46
Slide 47
47
Slide 48
48
Slide 49
49
Slide 50
50
Slide 51
51
Slide 52
52
Slide 53
53
Slide 54
54
Slide 55
55
Slide 56
56
Slide 57
57
Slide 58
58
Slide 59
59
Slide 60
60
Slide 61
61
Slide 62
62
Slide 63
63
Slide 64
64
Slide 65
65
Slide 66
66
Slide 67
67
Slide 68
68
Slide 69
69
Slide 70
70
Slide 71
71
Slide 72
72
Slide 73
73
Slide 74
74
Slide 75
75
Slide 76
76
Slide 77
77
Slide 78
78
Slide 79
79
Slide 80
80
Slide 81
81
Slide 82
82
Slide 83
83
Slide 84
84
Slide 85
85
Slide 86
86
Slide 87
87
Slide 88
88
Slide 89
89
Slide 90
90
Slide 91
91
Slide 92
92
Slide 93
93
Slide 94
94
Slide 95
95
Slide 96
96
Slide 97
97
Slide 98
98
Slide 99
99
Slide 100
100
Slide 101
101
Slide 102
102
Slide 103
103
Slide 104
104
Slide 105
105
Slide 106
106
Slide 107
107
Slide 108
108
Slide 109
109
Slide 110
110
Slide 111
111
Slide 112
112
Slide 113
113
Slide 114
114
Slide 115
115
Slide 116
116
Slide 117
117
Slide 118
118
Slide 119
119
Slide 120
120
Slide 121
121
Slide 122
122
Slide 123
123
Slide 124
124
Slide 125
125
Slide 126
126
Slide 127
127
Slide 128
128
Slide 129
129
Slide 130
130
Slide 131
131
Slide 132
132
Slide 133
133
Slide 134
134
Slide 135
135
Slide 136
136
Slide 137
137
Slide 138
138
Slide 139
139
Slide 140
140
Slide 141
141
Slide 142
142
Slide 143
143
Slide 144
144
Slide 145
145
Slide 146
146
Slide 147
147
Slide 148
148
Slide 149
149
Slide 150
150
Slide 151
151
Slide 152
152
Slide 153
153
Slide 154
154
Slide 155
155
Slide 156
156
Slide 157
157
Slide 158
158
Slide 159
159
Slide 160
160
Slide 161
161
Slide 162
162
Slide 163
163
Slide 164
164
Slide 165
165
Slide 166
166
Slide 167
167
Slide 168
168
Slide 169
169
Slide 170
170
Slide 171
171
Slide 172
172
Slide 173
173
Slide 174
174
Slide 175
175
Slide 176
176
Slide 177
177
Slide 178
178
Slide 179
179
Slide 180
180
Slide 181
181
Slide 182
182
Slide 183
183
Slide 184
184
Slide 185
185
Slide 186
186
Slide 187
187
Slide 188
188
Slide 189
189
Slide 190
190
Slide 191
191
Slide 192
192
Slide 193
193
Slide 194
194
Slide 195
195
Slide 196
196
Slide 197
197
Slide 198
198
Slide 199
199
Slide 200
200
Slide 201
201
Slide 202
202
Slide 203
203
Slide 204
204
Slide 205
205
Slide 206
206
Slide 207
207
Slide 208
208
Slide 209
209
Slide 210
210
Slide 211
211
Slide 212
212
Slide 213
213
Slide 214
214
Slide 215
215

About This Presentation

An exercise in the use of absolute and relative references in formulas so you can process large batches of data faster, broken down into very basic steps so that even Excel newbies can try doing it.

Size: 16.36 MB
Language: en
Added: Nov 30, 2017
Slides: 215 pages

Slide Content

How to Create a
1,000 x 1,000 Multiplication Table
in 5 minutes or less

by Theodicia “Tedi” Soriano ?

An exercise in absolute and
relative reference
in Excel Formulas

So you can process large
amounts of data very fast

Step 1

* Open Microsoft Excel

step 3: Type “1”
—

Home | Insert Pagetayout Formulas DA Review à

G ace can Mew |
Là Copy i i}
ia one - we = ES

Clipboard)

Step 4:"Go to CellB1

DATA

‘TEDI

E a

Step 8: Click on Cell B1

Step 9: Then type +1”

cupagare PRES nues
ae

SUMPRODUCT

Step 10

» Press “Enter” in your keyboard

DATA

‘TEDI

How to get the Maximum
Column

(aka the 1,000 Column)

step Tr. ji: on Ae

Step 12: Type ne | dl
J Cipbese is — |

_SUMPRODUCT x

LA

You can replace “1000” in
the formula with any number
you wish to use |

small note about the formula

lam using 26 as the divisor
since there are 26 letters
fromA to Z

small note #2 about theformula »

Step 13

» Press “Enter” in your keyboard

DATA

‘TEDI

Step 14:
Remember, the.resulting.number

82 , | -MOD(1000,26)

Step 15° Get the equivalent letter

°1=A 10=J 19=S
° 2=B 11=K 20=T
°3=C 12=L 21=U
°4=D 13=M 22=V
°5=E | 14=N 23=W
° 6=F 15=0 24=X

°7=G 16=P 25=Y DATA

A

Step 17: ve “=41000/26”

Cli pboa: d

You can replace “1000” in
the formula with any number
you wish to use |

small note about the formula

lam using 26 as the divisor
since there are 26 letters
fromA to Z

small note #2 about theformula »

Step 18:"Go to Cel C2

Step 19; Get the whole number
from,CelL.B3 result

Clipbpard al
NM SUMPRODUCT per

Step 20, Use that number in a
MOD.formula

Clipboard
eee

lam using 26 as the divisor
since there are 26 letters
fromA to Z

small note about the formula

Step 21

» Press “Enter” in your keyboard

DATA

‘TEDI

Sen}:
Remember the ses ane: number

ay E

Step 23: Get the equivalent letter

°1=A 10=J 19=S
° 2=B 11=K 20=T
°3=C 12=L 21=U
°4=D 13=M 22=V
°5=E | 14=N 23=W
° 6=F 15=0 24=X

°7=G 16=P 25=Y DATA

A

u 24: Go to EEE C3

Step ESA pe 72"

| woth mom

SS Bf 3
| SUMPROD

ELA

Step 26; Get the/whole number
from,Cell,B3

Ee ipboa rd
SuPRoOUET
A

Step 27. Divide'it ‘by 26

Clipbe2ré pis |
PE —
u SUMPRODUCT

lam using 26 as the divisor
since there are 26 letters
fromA to Z

small note about the formula

Step 28

» Press “Enter” in your keyboard

DATA

‘TEDI

Step 29; Get the/resulting whole
number

You can repeat steps 19-29 in
a different column

until it results in

a whole number

Step 30: Get the equivalent letter

°1=A 10=J 19=S
° 2=B 11=K 20=T
°3=C 12=L 21=U
°4=D 13=M 22=V
°5=E | 14=N 23=W
° 6=F 15=0 24=X

°7=G 16=P 25=Y DATA

. 8-H 17=Q 26=Z |
À

Step 31 + Combine the Equivalent
Letters

Maximum Column = ALL

step 32. Click on Cell B2

c WW F
1 1 2 3
va C1 ier
3 =. | 38.46154 1.461538 |
‘ iB

Step 33
+ Press “Shift” in your keyboard

DATA

‘TEDI

|
Step 34

- Keep pressing down “Shift” while
you do Step 35

DATA

‘TEDI

Step 35. Click ón Cell C3

Notice that we have selected
Cells B2, B3, C2 and C3

|
Step 36

* Remove your finger from pressing
“Shift”

DATA

‘TEDI

Step 37

+ Press “Delete” in your keyboard

DATA

‘TEDI

Notice that we cleared-out
the four cells we selected

Insert...

Delete...

Notice how Cell C1 is now
highlighted by a dashed line

step 40:"Click on the Name Box

ΠPER: A ma: RE

a # ce cau Jura = Ree

— da copys

Book! = Microsoft Excel
View Developer

Wap text General

Framatpainter BI Ud? EE lees cenar BI % :

ely a o jalo je]

Step AY. sn ka ”

P 7 Savery” 1 Sa ;
aste = ad
+ Format Painter B Z u ee S

Font

DATA

‘TEDI

Remember that “ALL” is the
Maximum Column
or 1,000‘ Column

Step 42

» Press “Enter” in your keyboard

DATA

‘TEDI

Notice that you have moved
to Cell ALL1

————
LA 1 JO

Y Home Insert Page Layout Formulas Data

Il

Step 43

+ Press “Ctrl” in your keyboard

DATA

‘TEDI

|
Step 44

+ Keep pressing “Ctrl” when you do
Step 45

DATA

‘TEDI

Step 45

+ Press “V” in your keyboard

DATA

‘TEDI

Notice that it has pasted the
same formula from Cell C1

It gets the value of the cell on
its left and adds 1

|
Step 46

* Remove your finger from pressing
“Ctrl” on your keyboard

DATA

‘TEDI

|
Step 47

+ Press “Ctrl” and “Shift” in your
keyboard

DATA

‘TEDI

|
Step 48

* Continue pressing on “Shift” and
“Ctrl” while you do Step 49

DATA

‘TEDI

Step 49

+ Press “€” in your keyboard

DATA

‘TEDI

Notice how it has highlighted
all the cells from C1 to ALL1

|
Step 50

+ Remove your fingers from pressing
“Ctrl” and “Shift”

DATA

‘TEDI

Step 51

+ Press “Ctrl” in your keyboard

DATA

‘TEDI

|
SED 52

- Keep pressing on “Ctrl” when you do
step 53

DATA

‘TEDI

Step 53

» Press “V” on your keyboard

DATA

‘TEDI

Notice that, these steps have
pasted the formulas on the
selected cellsıC1 to ALL1

The repeated us of the
formula added-up to 1,000

|
Step 54

+ Remove your fingers from pressing
“Ctrl”

DATA

‘TEDI

|
STEP 55

+. RIGHT-Click on the highlighted cells
Cito ALL1

DATA

‘TEDI

Step 56. Select “Copy”

Fromstronter| © A | EE > D dee “FE FE | tenor & cer
Huile Le a

Notice how the selected cells
now have a blinking
dashed-line border

E - AY EXA ES ES Merge aicentet = | - ES

Aonment
—

Al

|
Step 57

+. RIGHT-Click on the highlighted cells
Cito ALL1

DATA

‘TEDI

Step 58.Under “Paste Options”
Select.the,button.with.<123”

DATA

‘TEDI

Notice how the formulas are
now replaced by actual values

Clipboard fa 06
ALL

Step 59:"Click on Cell A2

_ A2

AD Ia

‘TEDI

Step 60: Type “2”

Clipboard
oe

Step 61: Click on cell A3

Clipboard pi Font
m — — = eer
A3

ie 63: ad on ES A2

Step 64: as ci 2].

Clipboard eu
SUMPRODUCT ve

zu =
en

Step 65:

» Press “Enter” in your keyboard

DATA

‘TEDI

Step 66:°RIGHT-click on cell A3

~ Clipbestt ‘lll au
= =

Step 67. Select EM

Ez

Notice how the border is
blinking dash lines

DATA

‘TEDI

Step 68: Click on the Name Box

“TS” $ Format Painter | “À al
Clipboard
=
A3

DATA

‘TEDI

Do NOT use commas when
you type “A1000”

Step 70:

» Press “Enter” in your keyboard

DATA

‘TEDI

You just went to cell A1000

ar J 7
CCS

Allgnment _

Step 71: RIGHT “Click on cell A1000

ipod m [don

¡gar
CEE
A1000
992 i
993
994 4
| EM
996

DATA

| ‘TEDI

Step 72s Under “Paste Options”
Select the “Paste” button

9911 : =

DATA

‘TEDI

You have pasted the same
formula from cell A3

[aa

B

= |
|
Wi... 3

The formula add 1 to the
value of the cell above it

Step 73° RIGHT “click on cell A1000

Clipboard
—

step 74:"Select “Copy”

ae = in
ib pt A Be %

Paste Special...

= DATA

Insert... |
V
À

Notice how the cell A1000
gets a blinking border of
dashed lines

|
Step 75:

+ Press “Ctrl” and “Shift” in your
keyboard

DATA

‘TEDI

|
Step 76:

- Keep pressing “Ctrl” and “Shift”
while doing Step 77

DATA

‘TEDI

Step 77

» Press “7” in your keyboard

Notice that you have selected
all cells from A3 to A1000

|
Step 78

+ Remove your fingers from pressing
“Ctrl” and “Shift” in the keyboard

DATA

‘TEDI

Step 79

+ Press “Ctrl” in your keyboard

DATA

‘TEDI

|
Step 80

- Keep pressing “Ctrl” while doing
Step 81

DATA

‘TEDI

Step 81

+ Press “V” in your keyboard

DATA

‘TEDI

Notice how the same formula
is pasted in cells A3 to A1000

2 A Y =A999+1
Bil 0

me DATA

‘TEDI

The repetitive adding of +1
has accummulated to 1,000

|
Step 82

+ Remove your fingers from pressing
“Ctrl”

DATA

‘TEDI

Step 83
+. RIGHT-click on the highlighted cells

DATA

‘TEDI

|
w Te actct apy”

supe

o
Paste TA T > DATA
Insert... (| 0

Delete... I
Clear Contents V
À

Notice.how:.the selected,cells
get a blinking border of
dashed lines

DATA

‘TEDI

Step 85
+. RIGHT-click on the highlighted cells

DATA

‘TEDI

Allformulasiinicells'A3 to
A1000 are replaced with
actual values

How to Quickly Fill-out the
remaining 998,001 cells of the
Multiplication Table

step 87:"Click on cell B2

step 88:“Type "= #

Cipboata | WE

ES ea
| SUMPRODUCT

EA

Step 90: Press FA” in your
keyboard

Clipboard =
er ee —A
| SUMPRODUCT ‚€
N |

Notice how “$” appeared
before the column letter and
row number

Step 91: Press FA” in your
keyboard, again,

UCT LA

Notice that only the $ before
the row number remains

Step 92."Type **

Clipboard
SUMPRODUCT

Step 93:"Click on cell A2

Clipboard
Seer game =
SUMPRODUCT Toa

Step 94: Press FA” in the
keyboard

Clipboard à
es === = z
SUMPRODUCT un PA
LA E —
a 5
DATA

Notice how “$” appeared
before the column letter and
row number

Step 95: Press FA” in the
keyboard again

Clipboard

— SE: —
SUMPRODUCT

E ree

Notice that only the $ before
the row number remains

Step 96: Press “FA” in the
keyboard for the à time

mo rd

SUMPRODUCT

ee
se

Notice how the $ is now
before the Column Letter

Step IP.

+ Press “Enter” in the keyboard

DATA

‘TEDI

step 98: RIGHT-Elick on cell B2

u 99: Select Copy”

m
ae ” A NE % ae
\ | À MER

Notice how the cell now has
a blinking border of
dashed lines

step 100° Click "on the Name Box

| Clipboard
—

Step 101 TypeAEL1000”

| ww x

Step 102:

» Press “Enter” in your keyboard

DATA

‘TEDI

Notice that cell ALL1000 is
now selected

Step 103:

+ Press “Ctrl” in your keyboard

DATA

‘TEDI

|
Step 104:

- Keep pressing “Ctrl” while doing
Step 105

DATA

‘TEDI

Step 105

+ Press “V” in your keyboard

The formula from cell B2 has
been pasted into cell ALL1000

Step 106:
+ RIGHT-click on cell ALL1000

<r 1077 ‘Select “Copy”

al & (a |

Paste Special... .
‘EDI
A

Notice how the cell now has a
blinking border of
dashed lines

Step 108:
+ Press “Shift” in your keyboard

DATA

‘TEDI

|
Step 109:

+ Keep pressing “Shift” while doing
Step 110

DATA

‘TEDI

Step 110: Click’On cell B2

This selects all the cells from
B2 to ALL1000

|
Step PPE

* Remove your finger from pressing
“Shift”

DATA

‘TEDI

|
Step 112:

¢ RIGHT -click anywhere on the
selected cells

DATA

‘TEDI

Step 113: Under ¿Paste Options”
Selectithe button with, “fe

SR — => a
[nan

DATA

‘TEDI

This will paste the formula in
all 998,001 selected cells
from B2 to ALL 1000

RESASARRSSNSSSNISSS

TEDI

Additional steps for
Aesthetics

For a Better-looking Table

Step 114 Click ontell B2

Step 115: In the Header Tabs,
Click.on.*View”.

ein | 9 ~ 5 ee

Home Insert

= —

Page Layout Formulas Data

Vie
a sal ro: UN

Step 116: Under View,
Click»Ofes Freeze Panes” p=

Step 11h: Under ¿Freeze Panes”,

the rest dl

)B n

3) Freeze First Column

2 Keep the first column visible while scrolling DA A
> through the rest of the worksheet. O | 0

TED

This freezes all the cells
above and to the left of
cell B2, the selected cell

step 1187 Click on Cell A1

SED FP

+ Press “Ctrl” in your keyboard

DATA

‘TEDI

|
Step 120:

- Keep pressing “Ctrl” while doing
Step 121

DATA

‘TEDI

Step 121:

+ Press “A” in your keyboard

This selects cells A1 to
ALL1000

998 999

= 985014
026 | 986013

987012

988011
989010
990009
991008
992007
993006
994005

| 995004
996003

| 997002
998001

995000

I
V
À

|
Step 122:

+ Remove your finger from pressing
“Ctrl”

DATA

‘TEDI

|
Step 123:

+. RIGHT-click anywhere on the
selected area

DATA

‘TEDI

Step 124>Select “Format Cells”
Cee re |

¡BLE SA AS |
1 883072 984063

¡4 cu

¡da cor,

| (Bh. Paste Options:
I 248)

| Paste Special.

DATA

Bee m
Sert
Insert Comment

ii I
Pick down List... V
Detine Name

@ Hyperin A

It would open a new
mini-window

Step 125: Under the “Number”
tap Select “Number”

| | Number oven ele (ee Fr =.

Time se 1000 Separator (,)
Percentage
Fraction Negative numbers:

TEDI

Step 126: Check ¡on the box for
“Use.1000.Separator (,)”

= = A
a es

a me = 2
r 5
Text 1,234.10 a
Spedal (1,234.10) a
‘Custom

(1,234. 10)

Step 127: Type “0? for Decimal
Places

Format Celis a

Step 128: Click TOR”

DATA

ate Beet re pi y ol rs. Curres d of |
| V
| (Ex) Go) A

This makes the numbers
easier to read

2 98 6 j 85,014
979,104 981, 8 M 985,026 986,013
980,096 ) 986,024 987,012

981,088 984,055, 985,044 33 987,022 983,011
982,080 985,050 986,040 988,020 989,010
983,072 986,045 987,036 989,018 990,009
984,064 B 987,040 9 990,016 991,008
985,056 989, 991,014 992,007
986,048 98% 991,018 "993,006
987,040 2 E y

939,018 5
990,009 991,008
991,000 992,000 998,000 _ 999,000 1,000,000!

I
V
À

987

961,368 962,345 963,322 | 964,259
962,352 963,330 964,308 965,286
963,336 964,315 965,294 966,273

262,360
963,342
264,324

965,30
966,288
9 967,272

"867,270
968,252 969,238 970,224 971210 972,196 973,182
969,234 970,221 971,208 972,195 973,182 974,169

Step 130:

+ Press “J” in your keyboard

This resets the view back to
the first cells so the file is
more cohesive when [opened

A
Step 131:Click'on the “File”
Header,tab

Insert Pagelayouwt Formulas DMA Review Vies Developer

PER

‘ormation about Book]

El saveas
5 Open
( Close ay

Protect

Workbook- |
Recent 1
Prepare for Sharing A

New @ 3] Before sharing this file, be aware that it contains: 0

I
V
À

Fone feet
PC
; Information about Book1
GY Save As i _ mM A|
& Open ar, Permissions

Organize =
FE Desktop
[la Downloads,
#3 Dropbox
El Recent Places.

& SkyDrive

¡Google Drive IN

ms

Authors:

2 Hide Folders

3 4 ee

© | Documents library

Includes: 3 location

+ My Documents (27)
CAUsers\Tedi Saran
e Profile Presentations

D di Family

Theodicia Soriano

Save Thumbnail

rage: Add à tag

Tools +

[Cancel]

SE atpication Tab
Save as type [Excel Workbook ("asd

Authors: Theodicia Sereno:

7] Save Thumbnail

SED PIT
+ Enjoy your newly-accomplished skill
in Excel
Y Ps
‘Ber, e >
Zw ws 1
1
\ ~ o Y

ieee DATA

‘TEDI

End of Presentation
Tags
excel formula easytofollow shortcuts data big data analytics
Categories
Technology Marketing
Download
Download Slideshow Get the original presentation file
Quick Actions
Statistics
  • Views 83
  • Slides 215
  • Age 2924 days
Related Slideshows
8-top-ai-courses-for-customer-support-representatives-in-2025.pptx 11
8-top-ai-courses-for-customer-support-representatives-in-2025.pptx
JeroenErne2
46 views
7-essential-ai-courses-for-call-center-supervisors-in-2025.pptx 10
7-essential-ai-courses-for-call-center-supervisors-in-2025.pptx
JeroenErne2
46 views
25-essential-ai-courses-for-user-support-specialists-in-2025.pptx 13
25-essential-ai-courses-for-user-support-specialists-in-2025.pptx
JeroenErne2
37 views
8-essential-ai-courses-for-insurance-customer-service-representatives-in-2025.pptx 11
8-essential-ai-courses-for-insurance-customer-service-representatives-in-2025.pptx
JeroenErne2
33 views
Know for Certain 21
Know for Certain
DaveSinNM
20 views
PPT OPD LES 3ertt4t4tqqqe23e3e3rq2qq232.pptx 17
PPT OPD LES 3ertt4t4tqqqe23e3e3rq2qq232.pptx
novasedanayoga46
24 views
View More in This Category