MIS 180. SIMNET EXCEL WORK. Chapter One.

Excel 2013 Chapter 1 Creating and Editing Workbooks Last Updated: 2/4/15 Page 1

USING MICROSOFT EXCEL 2013 Guided Project 1-2

Guided Project 1-2 For this project, you edit and format a spreadsheet for a sales representative for Eller Software Services. The spreadsheet contains clients’ personal information and a listing of their product purchases.

Skills Covered in This Project

 Open and save a workbook.

 Enter and format text and numbers.

 Copy text using the Fill Handle.

 Create a formula using AutoSum.

 Apply a theme and Cell Styles.

 Merge and center titles and subtitles.

 Change orientation.

 Apply font attributes.

 Apply numeric formatting.

 Apply date formatting.

 Apply a border.

 Change zoom level.

 Hide and insert a row and adjust column width.

 Rename and apply color to sheet tabs.

 Use spell check.

 Apply page layout options.

1. Open the EllerSoftwareServices-1.xlsx start file.

NOTE: If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the

top of the workbook so you can modify it.

2. The file will be renamed automatically to include your name. Change the project file if directed to do so

by your instructor, and save it.

a. Press F12 to open the Save As dialog box.

b. Locate the folder where your files are located.

c. Rename the file in the File name area.

d. Click Save.

3. Apply a theme to the worksheet.

a. Click the Themes button [Page Layout tab, Themes group].

b. Choose Retrospect from the Office gallery.

4. Enter and format data.

a. Select E5, type MN, and press Enter.

b. Select E5 and point to the Fill Handle.

c. Click and drag the fill pointer to E13.

d. Select C5:C13.

e. Press Ctrl+1 to open the Format Cells dialog box.

f. Click the Number tab and click Special under Category.

g. Select Phone Number and click OK to close the Format Cells dialog box.

5. Use AutoSum to calculate the total of gross sales and adjust the reference range.

a. Type Total in A15.

b. Select cell I15.

c. Click AutoSum [Home tab, Editing group] and press Enter.

d. Select I15 and click the cell reference range in the Formula bar.

e. Edit the cell range to I13 and press Enter.

6. Apply Merge & Center to the title and subtitle of the worksheet.

a. Select A1:I1 and click the Merge & Center button [Home tab, Alignment group].

b. Select A2:I2 and click the Merge & Center button.

Step 1

Download start file

Excel 2013 Chapter 1 Creating and Editing Workbooks Last Updated: 2/4/15 Page 2

USING MICROSOFT EXCEL 2013 Guided Project 1-2

7. Apply Cell Styles.

a. Select A1 and click the Cell Styles button [Home tab, Styles group].

b. Select Title in the Titles and Headings category.

c. Select A2 and click the Cell Styles button and select Heading 2 in the Titles and Headings category.

d. Select A4:A15, hold down Ctrl, and then select B4:I4.

e. Click the Cell Styles button and select 40%- Accent 1 in the Themed Cell Styles category.

f. Select I15, click the Cell Styles button, and then select Total in the Titles and Headings category.

8. Apply diagonal rotation to text.

a. Select A4:I4.

b. Click the Orientation button [Home tab, Alignment group].

c. Select Angle Counterclockwise.

9. Apply font attributes.

a. Select the cell range A4:A15, hold down Ctrl, and click and drag to select B4:I4.

b. Click the Font size drop-down list [Home tab, Font group] and select 12.

c. Click the Font type drop-down list [Home tab, Font group] and select Verdana.

d. Click the Bold button [Home tab, Font group].

e. Select the non-adjacent cell ranges C5:C13 and E5:F13.

f. Click Center [Home tab, Alignment group].

g. Select A1:A2 and click the Increase Font Size button [Home tab, Font group] two times.

10. Apply numeric formatting to your spreadsheet.

a. Select I5:I15.

b. Click Comma Style [Home tab, Number group].

c. Select I5, press Ctrl, and select I15.

d. Press Ctrl+1 to open the Format Cells dialog box.

e. Select the Number tab, if necessary, and select the Accounting category.

f. Change the Symbol drop-down list to $.

g. Click OK to close the Format Cells dialog box.

11. Apply date formatting to your spreadsheet.

a. Select H5:H13.

b. Click the Short Date format from the Number Format drop-down list [Home tab, Number group].

12. Increase the width of your columns.

a. Select A4:I15.

b. Click the Format button [Home tab, Cells group].

c. Select AutoFit Column Width.

13. Press Ctrl+S to save the workbook.

14. Edit a cell.

a. Double-click cell G9 to activate edit mode.

b. Delete the word Software from the cell contents and press Enter.

15. Change the width of a column.

a. Click the column G heading to select the column.

b. Click the Format button [Home tab, Cells group] and select Column Width from the menu.

c. Enter 31.0 characters as the new width.

d. Click OK.

Excel 2013 Chapter 1 Creating and Editing Workbooks Last Updated: 2/4/15 Page 3

USING MICROSOFT EXCEL 2013 Guided Project 1-2

16. Hide an existing row and insert a new row and row heading.

a. Right-click row heading 10.

b. Choose Hide from the context menu.

c. Right-click row heading 11.

d. Choose Insert from the context menu to add a new row. The new row appears directly above the

originally selected row.

e. Select cell A11 and

type Hilary Marschke.

f. Enter the following

information into the cell

range B11:I11. (Figure 1-

101).

B11: 245 Third Avenue

C11: 3203555443

D11: Saint Cloud

E11: MN

F11: 56301

G11: Training

H11: 11/15/2015

I11: 750.00

17. Rename and color a sheet tab.

a. Double-click the Sheet1 tab.

b. Type the following name: MN Clients.

c. Press Enter.

d. Right-click the MN Clients tab.

e. Point to Tab Color to add a fill color to the

background of the sheet tab.

f. Select Orange Accent 1, Darker 50% (last

color in the fifth Theme Color column)

(Figure 1-102).

18. Save the workbook.

19. Apply a bottom border. a. Select A4:I4.

b. Press Ctrl+1 to open the Format Cells

dialog box.

c. Click the Border tab.

d. Select the fifth line style in the second

column and select Orange, Accent 1,

Darker 50% for the line color (last color in

the fifth column).

e. Select the bottom border button in the Border area (third button under the Border heading).

f. Click OK.

20. Change the zoom level.

a. Click the View tab.

b. Click Zoom.

c. Select the 75% Magnification level.

d. Click OK.

Excel 2013 Chapter 1 Creating and Editing Workbooks Last Updated: 2/4/15 Page 4

USING MICROSOFT EXCEL 2013 Guided Project 1-2

21. Select the Spelling button [Review tab, Proofing group] and correct any misspellings in the worksheet.

22. Preview the worksheet.

a. Select Print from the File tab.

b. Review the workbook layout and format, and then return to the worksheet.

c. Use the Zoom slider in the Status bar to return to 100% magnification.

23. Change Page Setup options.

a. Display the Page Layout tab and click the Page Setup launcher [Page Setup group].

b. Select the Page tab and click the Landscape radio button under Orientation.

c. Click the Fit to: radio button under Scaling, enter 1 page wide by 1 tall.

d. Select the Margins tab and click the Horizontally check box under Center on page.

e. Select the Header/Footer tab.

f. Click the Footer: drop-down button and select the MN Clients to print the sheet name in the footer.

g. Click OK.

24. Save and close the workbook (Figure 1-103).

25. Upload and save your project file.

26. Submit project for grading.

Step 3

Grade my Project

Step 2

Upload & Save