MODULE 5: EXCEL EXPERT - MANAGING WORKBOOKS AND APPLYING

FORMATS

General Note: This step-by-step process is generally applicable to Microsoft Excel

2016. If you are using a different version (previous or advance), there might be a
difference in the commands. Also, there would be a difference in executing the
commands since laptops have different key features especially if you are using a
Macbook.

Topic 2. Applying Custom Formatting and Layouts

Step by Step: Apply Custom Number Formats

1. OPEN ​the ​M5-Topic 2. Exercise 1.xlsx​ workbook file for this exercise.
2. SAVE t​ he workbook as ​M5-Topic 2. Exercise 1 Solution.xlsx​.
3. In the Number Formats worksheet, select the range ​C5:F5 ​and press C ​ trl+1 ​to open the
Format Cells ​dialog box. Click the ​Number ​tab if it is not active. In the Category list,
select ​Number ​with ​3 ​decimal places. Select the ​Use 1000 Separator (,) ​check box,
and in the list for Negative numbers, select the first occurrence of ​(1,234.210)​. Then, in
the Category list, click ​Custom ​and edit the formatting string in the Type text box
control to the following (see below): ​#,##0.000_);[Blue](#,##0.000).

4. Click ​OK​. Use the Number tab in the ​Format Cells ​dialog box to set the number formats
in the ranges listed in the table on the next slides, per the specification indicated. In all
instances you can directly select the ​Custom ​category and type in the number format.
However you can often “jump start” the process by using a predefined number format
first and then switching to the ​Custom ​category and making any final edits. If available,
a shortcut that will default to the desired format or get you close is shown below the
desired number format. After trying each shortcut, select the ​Custom ​category to make
sure you have the desired format and make any needed edits there.
5. Compare your results with the figure below and make any necessary adjustments.

6. Continue applying the additional formats specified in the following table, using the
instructions from step 4.
7. Go to cell ​C54 ​and press ​Alt+= ​to insert an AutoSum. Excel displays a sum of only
7:07:30, but clearly we have over 50 hours in our column. This is where time formats can
get frustrating. We need the elapsed time format. In other words, we need to put
brackets around the h in the format.
8. Edit the number format in cell ​C54 ​to be: ​[h]:mm:ss;@ ​NOTE: Placing brackets
around an “h” or an “m” in a time format allows that part of the time specification to go
past its normal ​rollover ​limit. So if you are summing minutes and need to show more
than sixty minutes, use [m] or [mm]. If you need to show more than 24 hours, use [h] or
[hh].
9. Compare your results with the figure below and make any necessary adjustments.

10. SAVE ​the workbook.

11. Leave the workbook open to use in the next exercise.
 ➔ Take Note:​ Excel has a precision limitation of 15 significant digits. If you go beyond
that, Excel starts putting in zeros for the least significant digits. This means that
standard 16-digit credit card numbers need to be stored as text. Do not try to format
them as numbers! You will not retain all of the data.

Step by Step: Use Advanced Fill Series Options

1. OPEN ​the ​M5-Topic 2. Exercise 1 Solution.xlsx​ ​workbook from the previous

exercise, if you closed it.
2. Select the ​Auto Fill ​worksheet. Select cell ​B4 ​and use the fill handle to fill down through
cell ​B27​. Notice how the quarters roll over correctly.
3. Select cell ​C4 ​and double-click the fill handle. Note the correctness.
4. Select ​D4 ​and double-click the fill handle. Notice that Excel will pick up on the Qtr and
not continue to extend based on the ordinal prefix. So, instead of “5th Qtr” in cell B8, we
see “1st Qtr”.
5. Select ​E4 ​and double-click the fill handle. This time Excel continued on to 5th Year, 6th
Year, and so on.
6. Select ​F4 ​and double-click the fill handle. Excel is smart enough to increment the
number after “Exercise.”
7. Select ​G4:G5 ​and double-click the fill handle. Excel will pick up on the pattern and
continue counting by fives.
8. Select ​H4 ​and double-click the fill handle. What happened? If you start with a number
and you want Auto Fill to increment by one you have some options:
a. Start with two numbers, for example 1 and 2.
b. Hold down the ​Ctrl ​key as you drag the fill handle.
c. Double-click the fill handle and then click the ​Auto Fill Options ​button at the
bottom-right corner of the filled range, and select the ​Fill Series ​option.
d. Use any of these three options to fill the range ​H4:H27 ​with numbers 1 through
27.
9. Select ​I4 ​and double-click the fill handle. Use the ​Auto Fill Options ​button to select
Fill Formatting Only​.
10. Select ​J4 ​and double-click the fill handle. Notice that the default fill for a time value is by
the hour.
11. Select ​K4:K5 ​and double-click the fill handle. If you need to fill in times by any other
increment, you’ll need to start with two times.
12. Select ​L4:L27 ​(don’t drag the fill handle) and on the Home tab, in the Editing group,
click the ​Fill ​drop-down arrow and select ​Series​. Change the Step value to ​2.5 ​(see
below). Click ​OK​.
13. Select ​M4:M27 ​and on the Home tab, in the Editing group, click the ​Fill ​drop-down
arrow and select ​Series​. Change Type to ​Growth ​and the Step value to ​2 ​and then click
OK​.
14. Select ​N4:N27 ​and on the Home tab, in the Editing group, click the ​Fill ​drop-down
arrow and select ​Series​. Leave Type as ​Linear ​and select the check box for ​Trend ​and
then click ​OK​.
15. Select ​O4:O27 ​and on the Home tab, in the Editing group, click the ​Fill ​drop-down
arrow and select ​Series​. Change Type to ​Growth ​and select the check box for ​Trend
and then click ​OK​. Notice how changing the Type to Growth allows Excel to correctly
figure out the appropriate growth equation—the numbers in column O match the
numbers in column M.
16. For our first use of Flash Fill, using the data in P4:P12 we want to extract from the first
dash to just before the second dash. Select ​Q4​, double-click the fill handle, click the
Auto Fill Options ​button, and select ​Flash Fill​. Excel correctly fills the data.

➔ Take Note:​ Most Flash Fill examples tend to be pretty simple, but the algorithms this
feature uses can do some pretty good guesswork. However, these algorithms are not
quite infallible.

17. Now, we want to extract data from the first dash until two characters after the second
(using P4:P12). Select ​R4​, double-click the fill handle, and then use ​Flash Fill​. This
time, Excel didn’t quite figure out what we wanted. Instead it guessed that our rule was
“first dash then five more characters”.
18. Select cell ​R6 ​and edit it to be ​-112-KN​. (Use the F2 key and add an “N” to the existing
value in R6. Otherwise Excel will think you’re entering a formula.)
19. Select cells ​R7:R12 ​and press the ​Delete ​key to clear the remaining cells that were
flash-filled incorrectly the first time.
20. Now select ​R4:R6​, double-click the fill handle, and then use ​Flash Fill​. This time Excel
is able to correctly figure out our rule.
 21. Select ​S4:S7​. Drag the fill handle over to column ​V​. Auto Fill is able to appropriately fill
multiple rows simultaneously.
22. Select ​S10 ​and drag the fill handle down to ​row 27 ​(Auto Fill will stop with Feb. 17th).
Then, from the ​Auto Fill Options ​button select ​Fill Months​. Notice that Auto Fill has
guessed you wanted the last day of each month.
23. Select cell ​T11 ​and drag the fill handle down to ​T27​. From the ​Auto Fill Options
button, select ​Weekdays​. Notice, our range has no Saturdays or Sundays.
24. Select ​V11:V36​. Click the ​File t​ ab, click ​Options​, select ​Advanced​, and then scroll
almost to the bottom of the dialog box. Click the ​Edit Custom Lists ​button.
25. The Import list from cells control should already display the range $V$11:$V$36. Click
the ​Import ​button. A list starting with “Black, Blue, Brown, Chartreuse” should appear
in the Custom lists box (see the figure below). Click ​OK​. Click ​OK ​in the ​Excel Options
dialog box.

26. In cell ​W11​, type ​Green ​and press ​Ctrl+Enter​. Double-click the fill handle. Excel fills
the range with the data from the custom list, beginning with Green.
27. SAVE ​the workbook and ​CLOSE i​ t.
28. LEAVE ​Excel open to use in the next exercise.

Step by Step: Using Input Messages and Error Alerts

1. OPEN ​the ​M5-Topic 2. Exercise 2.xlsx​ workbook file for this exercise.
2. SAVE ​the workbook as ​M5-Topic 2. Exercise 2 Solution.xlsx.​
3. Freeze rows ​1 ​through ​4 i​ n both worksheets in the workbook.
4. In the ​Client List ​worksheet, select column ​H​.
5. Click the ​Data ​tab and then in the Data Tools group, click ​Data Validation​. The ​Data
Validation d​ ialog box opens.
6. Click the ​Settings ​tab, if necessary.
7. In the Allow list box, choose ​Text length​. This is the first step in the creation of a rule
governing how many characters each new entry should contain.
8. In the Data list box, choose ​equal to​.
9. Click the ​Length ​box and type ​2 ​(see the figure below).

10. Click the ​Input Message ​tab. This tab displays a message when you select a cell in the
validation range.
11. Click the ​Title ​box and type ​Rule:​.
12. Click the ​Input message ​box and type ​Please enter the two-character state
abbreviation.
13. Click the ​Error Alert ​tab. Excel can display an error alert message when a user
attempts to enter data that is invalid.
14. Click the ​Title ​box and type ​Data Entry Error​.
15. Click the ​Error message ​box and type ​Only two-character state abbreviations
are recognized. ​This message is displayed in a dialog box whenever an invalid entry is
made in column H.
16. Click ​OK​.
17. To test the new validation rule, click cell ​H57​. You should see the notification message
you typed into the Input Message tab.
18. Type ​Ohio ​and press ​Enter​. Excel displays an alert dialog box with the error message
you created (see the figure below).
 19. Click ​Cancel​. The partial entry in cell H57 is erased.
20. SAVE ​the workbook and ​LEAVE ​it open for the next exercise.

Step by Step: Using Lists for Data Validation Criteria

★ Many databases include fields that allow a specified number of valid entries.
★ In data entry, it’s easy for someone to slip and type an invalid entry.
★ You can preempt events like this by building a rule that restricts entry to a handful of
valid entries.

1. OPEN ​the ​M5-Topic 2. Exercise 2 Solution.xlsx​ w ​ orkbook from the previous

exercise, if you closed it.
2. Click the ​Patient List ​sheet tab.
3. Select column ​B​.
4. Click the ​Data ​tab and then in the Data Tools group, click ​Data Validation​.
5. In the ​Data Validation d ​ ialog box, click the ​Settings ​tab.
6. In the Allow list box, choose ​List​. The Source box appears at the bottom of the dialog
box.
7. Click the ​Source ​box. Type ​Dog,Cat,Other ​being careful to include the commas.
8. Uncheck the ​Ignore blank ​box (see below).
9. Click the ​Input Message ​tab. Click the ​Title ​box and type ​Rule:​. Then, click in the
Input message ​box and type ​Choose from Dog, Cat, or Other​.
10. Click ​OK​. Now anyone entering a new patient into the database must specify the animal
type from a drop-down list in column B. We’ll test this validation rule later in this
exercise.
11. Next, we’ll specify a validation list for another column in the database. Select column ​E
(Owner #).
12. In the Data Tools group, click ​Data Validation​.
13. Click the ​Settings ​tab. In the Allow list box, click ​List​.
14. On the right side of the Source box, click the ​Collapse Dialog ​button.
15. With the ​Data Validation d ​ ialog box collapsed, click the C​ lient List ​worksheet tab.
16. Select column ​A ​(Client #).
17. At the end of the Source box, click the ​Expand Dialog ​button. The full dialog box
returns, and the Source box should now read ​=’Client list’!$A:$A.
18. Uncheck the ​Ignore blank ​and ​In-cell dropdown ​boxes.
19. Click the ​Error Alert ​tab. Choose ​Warning f​ rom the Style box.
20. In the Error message box, type ​Owner must be the number for a pre-existing
client​.
21. Click ​OK​. Now the Owner # column may contain only numbers for clients who appear in
the Client # column of the Client List worksheet.
22. To make sure your new validation rules are working, in the Patient List worksheet, at the
bottom of the list, click cell ​A57 ​and attempt to type the following data:

23. After you attempt to enter ​Puppy ​into column B, respond to the error message by
clicking ​Cancel ​and by selecting ​Dog ​from the drop-down list.
24. After you attempt to enter ​61 ​into column E, respond to the error dialog box shown in
the figure below by clicking ​No ​and typing ​31​.
 25. SAVE ​the workbook and ​CLOSE i​ t.
26. LEAVE Excel open to use in the next exercise.

➔ Take Note:​ It’s still feasible for an invalid value to remain in a worksheet after the
user has been warned that it’s invalid. For example, in the previous steps if you were to
click Yes instead of No, the value 61 would remain in column E, even though there is no
client numbered 61 in column A of the Client List worksheet. Conceivably, this way you
can purposefully enter a new canine patient into the list without an owner, if you
intend to add the owner’s information later.

Step by Step: Record and Modify a Basic Macro

1. OPEN the ​M5-Topic 2. Exercise 3.xlsx​ workbook file for this exercise. If the
Developer tab is already displayed on the ribbon, skip to step 5.
2. Click the File tab and then click Options.
3. In the ​Excel Options d​ ialog box, click Customize Ribbon.
4. In the Main Tabs list on the right, select the Developer check box. This adds the
Developer tab to the Excel ribbon, enabling you to more easily record macros. Click OK.
5. The macro that you record creates a custom subtotal row at the place you define, rather
than at some place Excel determines. The rule you follow is that the user (you) must
select the cell where you want the subtotal to appear, and then run the macro. So to
prepare for macro recording, click cell D20.
6. Click the ​Developer ​tab and then in the Code group, find ​Use Relative References​.
If it is not highlighted, click to select it. You want relative references for this macro.
7. In the Code group, click ​Record Macro​.
8. In the ​Record Macro d ​ ialog box, click the ​Macro name ​box and replace the existing
text with ​CustomSubtotals​.
9. In the Shortcut key box beside Ctrl+, type the capital ​S​. This changes the shortcut key to
Ctrl+Shift+S. Leave Store macro in set to This Workbook. In the Description box, type
Creates custom subtotal rows​. The ​Record Macro d ​ ialog box should now appear as
shown here.

10. Click ​OK​. You are now recording a macro.

➔ Troubleshooting: ​If you mess up a step during the macro recording, don’t worry.
Click Stop Recording in the Code group of the Developer tab. Then start again from
step 5. Use the same name, and when Excel asks whether you want to overwrite the
existing macro with the same name, respond with Yes.

11. Press ​Shift+Down Arrow​.

12. Click the ​Home ​tab and then in the Cells group, click the ​Insert arrow​. In the menu,
click ​Insert Sheet Rows​.
13. Press ​Shift+Up Arrow​.
14. In the Editing group, click ​AutoSum​. Do not press Enter.
15. In the Clipboard group, click ​Copy​.
16. Press ​Tab​.
17. Type the partial formula ​=max(​.
18. In the Clipboard group, click ​Paste​.
19. Type ​) ​(end parenthesis) and then press ​Tab​.
20. Press ​Left Arrow​.
21. Click the ​Font Color arrow​, and select ​Dark Blue, Text 2 ​(first row, fourth column
under Theme Colors).
22. Click the ​Italic ​button.
23. Click the ​Developer ​tab and then in the Code group, click ​Stop Recording​. As the
figure below shows, the macro generates a total for the bottom of the arbitrary cluster of
records, and also tabulates the highest (maximum) value in that cluster in the cell
adjacent to the subtotal.
24. On the Developer tab, in the Code group, click ​Visual Basic​. You will make a simple
edit to the macro code that doesn’t require knowledge of VBA.
25. In the left pane of the Microsoft Visual Basic for Applications window, expand the
Modules ​folder, if necessary (click the + sign). Then, double-click ​Module1​. The macro
code displays in the Code window on the right.
26. In the Code window, locate the code line that reads “Selection.Font.Italic = True” near
the end of the code listing. Drag over the text ​Italic ​and type ​Bold ​in its place. Be careful
not to make any other changes to the code.
27. In the menu bar and the top of the window, click ​File ​and then select ​Close and
Return to Microsoft Excel​.
28. In the worksheet, delete rows 20 and 21. You will test the macro using the assigned
shortcut key and ensure that the macro modification works correctly.
29. Select cell D20 and then press Ctrl+Shift+S. The macro runs and the result in cell E20
appears in bold rather than italic formatting.
30. Click the File tab and then click Save As. Select a folder or click Browse to navigate to the
folder where you are saving your files.
31. In the Save As dialog box, under Save as type, choose Excel Macro-Enabled Workbook
(*.xlsm). Save the workbook as ​M5-Topic 2. Exercise 3 Solution.xlsm​. Click OK if
you see a message box displayed after you save the file.
 ➔ Take Note:​ For security reasons, Excel no longer saves macros in its regular .XLS and
.XLSX file formats. Now, the only way to save a macro-enabled workbook is to give it
the special .XLSM file type. This way, companies that want to avoid any possibility of
spreading malware can enforce policies preventing .XLSM files from being attached to
or received within e-mails.

Step by Step: Manage Macro Security

1. OPEN ​the ​M5-Topic 2. Exercise 3 Solution.xlsm​ ​workbook from the previous

exercise, if you closed it. Or, you can open a blank workbook.
2. On the Developer tab, in the Code group, click ​Macro Security​.
3. In the ​Trust Center d​ ialog box, click ​Disable all macros with notification ​to have
Excel warn you whenever an opened workbook contains macros, enabling you to turn
those macros on or off based on your decision. This is the recommended setting for most
situations (see the figure below).

4. Click ​OK​.
5. SAVE t​ he workbook and ​CLOSE i​ t.
6. LEAVE Excel open to use in the next exercise.

Step by Step: Insert Form Controls

1. OPEN ​the ​M5-Topic 2. Exercise 4.xlsx​ workbook file for this exercise.
2. SAVE t​ he workbook as ​M5-Topic 2. Exercise 4 Solution.xlsx​.
​ eveloper ​tab. In the Controls group, click the ​Insert ​button. Select the
3. Click the D
ActiveX Combo Box ​control.
4. Place the mouse pointer close to the top left corner of cell ​E4​. Before you click the
mouse, press the ​Alt ​key. Then click the mouse and drag the pointer just above and to
 the left of the bottom right corner of cell ​E4​. Release the mouse button first, then release
the ​Alt ​key.
5. Click the ​Combo Box control ​you just inserted into E4 to select it, if necessary. If you
do not see the six circular resizing handles around the borders of the control, then click
the ​Design Mode ​button on the Developer tab.
6. Next to the Design Mode button on the ribbon is the Properties button. Click the
Properties ​button. The Properties window opens.
7. For the ​LinkedCell ​property, in the second column, type ​G4​.
8. For the ​ListFillRange ​property, type ​B3:B21​.
9. Change the ​ListRows ​property to ​12​. Compare your settings in the Properties window
with the figure below.

10. Close the Properties window and then click any cell on the worksheet.
11. Click the ​Design Mode ​toggle button to turn off Design Mode. Click the drop-down
arrow in ​E4 ​and select various values. The value of G4 should match your selection.
12. Try typing ​Taupe ​into the control and then press ​Enter​. Excel should accept the entry
and display ​Taupe ​in cell G4.
13. Select cells ​B3:B21​. In the Name Box on the left end of the formula bar, enter the name
lst.Colors ​and then press ​Enter ​(note that the first character in the name is a lowercase
“L” not the number 1).
14. Use the process in step 13 to assign the following names to the indicated cells as shown
below (the first character in each name is a lowercase “L” not the number 1):
15. On the Developer tab, in the Controls group, click ​Insert ​and then select the ​ActiveX
Check Box ​control.
16. Place the mouse pointer close to the top left corner of cell ​E6​. Before you click the
mouse, press the ​Alt ​key. Then click the mouse and drag the pointer just above and to
the left of the bottom right corner of cell ​E6​. Release the mouse button first and then
release the ​Alt ​key.
17. Click the ​Check Box control ​you just inserted into E6 to select it, if necessary. If you
do not see the six white circular resizing handles around the borders of the control, then
click the ​Design Mode ​button on the Developer tab.
18. Click the ​Properties ​button. For the Caption property, type ​Matte Finish? ​For the
LinkedCell property, type ​lnk.Checkbox.X
19. Turn off ​Design Mode ​and test the check box control.
20. Repeat previous instructions as appropriate to add the following controls:
a. An ​ActiveX Text Box control ​in ​E8 ​with a LinkedCell property of
lnk.Textbox.X
b. An ​ActiveX List Box control ​in ​E11:E21​, LinkedCell property is
lnk.Listbox.X ​and the ListFillRange is ​lst.Colors
c. An ​ActiveX Option Button ​control in ​E23​, LinkedCell property is
lnk.Option1.X ​and the Caption is ​Matte Finish
d. An ​ActiveX Option Button ​control in ​E25​, LinkedCell property is
lnk.Option2.X ​and the Caption is ​Glossy Finish
e. An ​ActiveX Option Button ​control in ​E27​, LinkedCell property is
lnk.Option3.X ​and the Caption is ​No Finish
21. Close the Properties window, turn off Design Mode, and test your controls.
22. On the Developer tab, click the ​Insert ​button. This time insert a ​Form control
Combo Box ​into cell ​I4​.
23. Right-click the ​combo box control ​and select ​Format Control ​from the shortcut
menu. Select the ​Control ​tab.
24. Set the Input range to ​lst.Colors ​and the Cell link to l​ nk.ComboBox.F ​and then click
OK​.
25. Click any regular cell. If the pointer changes into a pointing finger when you mouse over
the control in cell I4, then you can use it.
26. Try typing ​Taupe ​into the ​Forms combo box i​ n cell I4. Unlike the ActiveX combo
box, you can’t type into a Forms combo box.
27. Place a ​Form control check box ​in ​I6​.
28. Right-click the check box and select ​Format Control​.
29. On the Control tab, set the Cell link to ​lnk.Checkbox.F​.
30. Click the ​Alt Text ​tab and change the alternative text to M
​ atte Finish? ​Click O
​ K​.
31. Click in cell ​H6 ​then press the ​right-arrow ​key once. Then press ​Delete​. This will clear
out the text in ​I6 ​so you can read the check box better.
32. Notice that changing the Alt Text in step 30 didn’t change the check box’s caption.
Right-click the ​check box control ​and select E ​ dit Text ​to change the caption to
Matte Finish?​.
33. Place a ​Form control list box ​across cells ​I8:I18​. (Remember, if you press the Alt key
prior to starting your drag, the control will snap to the cell borders.)
34. Right-click the ​list box ​and select ​Format Control ​from the shortcut menu. Make the
following entries:
a. Input range = ​lst.Colors
b. Cell link = ​lnk.Listbox.F
35. Click ​OK ​and then select any cell in the worksheet. Test your Forms list box control.
36. Delete the text in cells ​I20​, ​I22 a
​ nd ​I24
37. Place a ​Form control Option Button ​in cell ​I20 ​and edit the text to read ​Matte
Finish​.
38. Place a ​Form control Option Button ​in cell ​I22 ​and edit the text to read ​Glossy
Finish​.
39. Place a ​Form control Option Button ​in cell ​I24 ​and edit the text to read ​No Finish​.
40. Right-click any of the Option Button controls and select ​Format Control​. Click the
Control tab, set the Cell link to ​lnk.Option.F​, and then click ​OK​. Your worksheet
should now look similar to the figure below.
 41. Play with the controls a bit and notice that the ActiveX combo box and list box controls
are returning the text you select. Compare this to the Form combo box and list box. Both
of those return the index (position) of your choice in the list of choices.
42. SAVE ​the workbook and ​CLOSE i​ t.
43. LEAVE ​Excel open to use in the next exercise.

★ Note that in the case of option button controls, all three Form control option buttons
have the same linked cell and this linked cell contains the index of whichever option
button is selected.
★ Compare that to the ActiveX option buttons where each option button has its own linked
cell and each linked cell toggles between TRUE and FALSE.

➔ Take Note:​ If you accidentally double-clicked any of the ActiveX controls in the
process of inserting them, depending on the type of control, Excel will automatically
insert either a stub _Click Event Handler procedure or a stub _Change Event Handler
procedure into code (class) module for the worksheet. You may have just closed the VBE
or switched back to Excel without really noticing that this happened. If you did this,
then you’ll get a message from Excel telling you that a VB project cannot be saved in a
macro-free workbook. Just click Yes to go ahead and save the workbook. The
auto-generated event-handler stub will automatically be discarded.

Step by Step: Apply International Symbols and Currency

1. OPEN ​the ​M5-Topic 2. Exercise 5.xlsx​ workbook file for this exercise.
2. SAVE t​ he workbook as ​M5-Topic 2. Exercise 5 Solution.xlsx.​
3. Click cell ​C7​, press ​F2​, and then press H
​ ome ​to enter edit mode and position the cursor
at the beginning of the cell’s text.
4. Press the ​right arrow key ​twice to position the cursor directly after the first “a” and
then press the ​Backspace ​key to delete the “a”.
5. Click the ​Insert ​tab. On the right end of the tab, notice that the ​Symbol ​button is still
enabled while in edit mode. Click the ​Symbol ​button. The ​Symbol d ​ ialog box opens.
6. In the Font box at the top-left corner of the dialog box, the Font should be Calibri. If it is
anything other than Calibri, change it to ​Calibri​.
7. In the top-right corner of the dialog box, ensure that ​Basic Latin ​is the selected Subset.
(If you don’t see the Subset drop-down box, then select Unicode (hex) in the from:
drop-down box in the lower-right corner of the dialog box.)
8. Locate and click the ​ã ​character, and then click the ​Insert ​button. Compare your screen
with the figure below and then click ​Close​. Press ​Enter ​to complete the edit. The text in
cell ​C7 ​has changed from “Sao Paulo” to “São Paulo.”
➔ Take Note: t​ he Subset control jumps you to the first character in a subset; it’s not a
filter. There is a subset jump for Currency Symbols, and using that jump will get you
close to the Euro Symbol (€). However, if you need the British Pound (£) or the
Japanese Yen (¥), those are much higher in the table.

9. Use the Symbol button on the Insert tab to make the following edits to the specified cells:

10. Select cells ​D12:K16​. Click the ​Home ​tab and then in the Number group, click the
drop-down arrow ​beside the $ button and select ​£ English (United Kingdom)​.
Click the ​Decrease Decimal ​button twice so the numbers display no decimals.
11. Select cells ​D17:K21​. Press ​Ctrl+1 ​to open the ​Format Cells d​ ialog box. Click the
Number ​tab, if necessary.
12. In the Category list, select ​Accounting​. Change the Decimal places to ​0​. In the Symbol
drop-down list, select ​¥ Japanese (​ unfortunately, the Symbol list is not sorted
alphabetically so you just have to scroll until you find it). Click ​OK​.
13. Select ​D7:K11 ​and then press ​Ctrl+1 ​again.
14. Set the number format to ​Accounting ​with ​0 ​Decimal places. Leave the Symbol as a
dollar sign.
15. In the Category list, change the category to ​Custom ​and then edit the number format,
placing a backslash and an R in front of each $ symbol so that it reads:
_(​\R​$* #,##0_);_(​\R​$* (#,##0);_(​\R​$* “-”_);_(@_)
16. Click ​OK​. Press ​Ctrl+Home ​and then compare your worksheet with the figure below.

17. SAVE ​the workbook and ​CLOSE i​ t.

18. CLOSE E ​ xcel.