Pengantar Teknologi Informasi – Pertemuan 12 – Materi Perkuliahan

Gede Surya Mahendra - Home

Pengantar Teknologi Informasi – Pertemuan 12 – Materi Perkuliahan

Stiki Logo - Short

Mata Kuliah : Pengantar Teknologi Informasi

Kode Mata Kuliah : MPB-201

Program Studi : Teknik Informatika



Untuk tampilan lebih baik, dapat mendownload file PDF pada link yang tersedia

Download File Materi PDF







Materi Ajar
Microsoft Office Specialis (MOS)

(Pertemuan 12)

Mg Ke- Kemampuan Akhir yang Diharapkan Bahan Kajian Metode Pembelajaran Waktu Pengalaman Belajar Penilaian dan Indikator Bobot Penilaian (%)
(1) (2) (3) (4) (5) (6) (7) (8)
12,13 Mahasiswa mampu memahami dan mengerjakan soal terkait sertifikasi Microsoft Office Specialist (MOS) Ketepatan mengerjakan soal-soal sertifikasi MOS, yang mana membahas tentang penggunaan Microsoft Excel dalam bekerja. Pembelajaran Project Based Learning dalam mengerjakan soal yang diberikan. [TM : 2x(2×50”)] Mampu menggunakan dan mengerjakan soal sertifikasi terkait dengan penggunaan Microsoft Excel dalam bekerja. Kemampuan dalam menggunakan dan mengerjakan soal sertifikasi terkait dengan penggunaan Microsoft Excel dalam bekerja, seperti membuat tabel, grafik, perhitungan di Excel dll. 10%

Create Worksheets and Workbooks

The practice file for these tasks is located in the MOSExcel2016\Objective1 folder. The folder also contains a subfolder of result files that you can use to check your work.

  1. Create worksheets and workbooks practice tasks

Get Ready use Excel_1-1 folder

Step by step :

  1. Open the Excel_1-1 workbook and move the Source Data worksheet as the last worksheet in the workbook.
  2. From the Start screen, create a new workbook based on the built-in Blank workbook template. Save the document in the practice file folder as MyBlank.xlsx.
  3. Add a copy of the Source Data worksheet on Excel_1-1 workbook to the open MyBlank workbook as the first worksheet in the workbook.
  4. Create a new worksheet Business Price that located after Source Data worksheet on MyBlank workbook.
  5. Import data business price index.csv with comma separated values into Business Price worksheet on MyBlank workbook.
  6. In the open workbook, display the New page of the Backstage view. Locate the online template for a mortgage payment calculator. Create a workbook based on this template. Save the workbook in the Excel_1-1 folder as MyCalc.xlsx.
  7. Save the Excel_1-1, MyBlank workbooks.
  8. Navigate in worksheets and workbooks

Get Ready. Open Excel_1-2 for this lesson

Step by step :

  1. Open the Excel_1-2 workbook and search the workbook for all instances of the word garden and replace with word farm. Confirm that the search returns results from both worksheets.
  2. Display the Product List worksheet on Excel_1-2 worksheet. Create a new Define Name Berry_bushes that refers to $A102:$C103
  3. Create a hyperlink from cell F13 to cell ranged named Berry_bushes
  4. Display the Employees worksheet. In cell C12, enter a hyperlink to the website located at which the cell displays Please visit our website instead of the URL.
  5. Format worksheets and workbooks

Get Ready. Open Excel_1-3 for this lesson

Step by step :

  1. Open the Excel_1-3 workbook and change the worksheet tab color
  • JanFeb with color Orange, Accent 1, darker 25%
  • MarApr with color Brown, Accent 3
  • MayJun with color Ice blue, Background 2, Darker 25%
  • JulAug with color Light Blue
  • SeptOct with color Green
  • NovDec with color Orange, Accent 1
  1. Change the worksheet names from JanFeb, MarApr, MayJun, JulAug, SepOct, and NovDec to Period1, Period2, Period3, Period4, Period5, and Period6

Display the Period1 worksheet and do the following:

  1. Delete column A (Day) and row 2 (Hour).
  2. Configure the worksheet to print at a Landscape orientation.
  3. Create a header that will print on all the pages of the worksheet. In the left header section, enter the Current Date property; in the center section, enter the File Name property; and in the right section, enter the Page Number property.
  4. Change the center section of the header to display the name of the worksheet instead of the workbook.
  5. Resize all columns to fit their content.
  6. Customize options and views for worksheets and workbooks

Get Ready. Open Excel_1-4 for this lesson

Step by step :

  1. Open the Excel_1-4 workbook, display the Inventory List worksheet, and Hide column A (the Inventory ID) and row 3 (the data sources)
  2. Add the Calculator button (which is not available on any ribbon tab) to the Quick Access Toolbar. Make it the leftmost button, and insert a separator between it and the other buttons.
  3. Create a Quick Access Toolbar for only the current workbook. Add the
  4. Insert Combo Chart, Insert Picture, and Insert Table buttons (all available on the Insert tab).
  5. Hide the By Product-Customer Filtered worksheet.
  6. Display the My Monthly Budget worksheet and Freeze rows 1 through 9 so that when you scroll the rest of the worksheet, those rows are always visible.
  7. Display the My Monthly Budget worksheet in Page Break Preview
  8. Attach the keywords (tags) spending to the workbook.
  9. On the Sales by Category worksheet, set the print area so that only cells A1:C42 print and configure the page setup options to print the worksheet gridlines

Manage Data Cells and Ranges

The practice file for these tasks is located in the MOSExcel2016\Objective2 folder.

  1. Insert data in cells and ranges practice task

Get Ready. Open Excel_2-1 for this lesson

Step by step :

  1. Open the Excel_2-1 workbook, and complete the following tasks by using the data in cells B4:G9 of the Ad Buy Constraints worksheet:
  2. Paste only the values and formatting into the range beginning at B18.
  3. Paste only the formulas into the range beginning at B25.
  4. Paste only the formatting (but not the content) into the range beginning at B32.
  5. Delete rows to move the headings to row 1.
  6. Delete columns to move the Magazine column to column A.
  7. Cut the data from the Mag3 row (B4:F4) and insert it into the Mag2 row (B3:F3).
  8. Move the Cost Per Ad data to the left of the Total Cost cells.
  9. Insert two blank cells in positions B8:B9, shifting any existing data down.
  10. Transpose the names in the Magazine column (cells A1:A6) to the first row of a new worksheet.
  11. On the Price List worksheet, do the following:
  12. Using the fill handle, fill cells A2:A21 with Item 1, Item 2, Item 3, and so on through Item 20.
  13. Fill cells B2:B21 with 10, 20, 30, and so on through 200.
  14. Fill cells C2:C21 with $3.00, $2.95, $2.90, and so on through $2.05.
  15. Copy the background and font formatting from cell A1 to cells A2:A21. Then delete the content of cell A1 (but not the cell).
  16. Format Cells and Ranges

Get Ready. Open Excel_2-2 for this lesson

Step by step :

  1. Open the Excel_2-2 workbook, display the Employees worksheet, and do the following: Merge cells A13:C14 so that the hyperlink is centered in a double-height cell across the three columns.
  2. On the Expense Statement worksheet, do the following:
  3. Select the entire worksheet and turn on text wrapping.
  4. Turn off text wrapping in only rows 4, 5, and 9.
  5. Right-align the entries in column A.
  6. Bottom-align the headings in row 9.
  7. Apply the Angle Counterclockwise orientation to the headings in row 9.
  8. Format cell K10 to display its contents as currency with a US dollar symbol and no decimal places. Then apply the same formatting to cells K11: K23.
  9. Apply the 20% – Accent2 cell style to cells A9:K9.
  10. Summarize and organize data

Get Ready. Open Excel_2-3 for this lesson

Step by step :

  1. Open the Excel_2-3 workbook. On the Order Details worksheet, use conditional formatting to do the following to all the values in the Extended Price column:
  2. Apply the 3 Arrows (Colored) icon set. (Keep the default settings.)
  3. Add blue data bars to the column. (Keep the default settings.)
  4. Fill all cells in the column that contain values of greater than $100 with yellow.
  5. On the JanFeb worksheet, do the following:
  6. Insert a row below the times for January. In that row, summarize the data for each hour by using a Column sparkline.
  7. Apply the Colorful #4 sparkline style.
  8. Accentuate the First Point and Last Point data markers.
  9. On the MarApr worksheet, do the following:
  10. In column P, summarize the data for each day of March by using a Line sparkline.
  11. Apply the Sparkline Style Accent 6, Darker 25% style.
  12. Display all the data markers without placing emphasis on any specific type of data marker.

On the Sales By Region worksheet, Create subtotals of sales amounts by period.


 48 total views,  2 views today

Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *