An Introduction to Spreadsheet by JMSK PSA

SARIZUN BINTI MOHAMAD SIDEK

SITI MARIAM BINTI RAJAB

NUR LIYANA BINTI KHALID KHAN

AN INTRODUCTION TO SPREADSHEET

ALL RIGHTS RESERVED. No part of this publication may be reproduced, distributed or transmitted in any form or by any means, including photocopying, recording or other electronic or mechanical methods, without the prior written permission of Politeknik Sultan Salahuddin Abdul Aziz Shah. COMPUTER APPLICATION SPREADSHEET Writers : Sarizun binti Mohamad Sidek Siti Mariam binti Rajab Nur Liyana binti Khalid Khan eISBN No: First Published in 2021 by: UNIT PENERBITAN Politeknik Sultan Salahuddin Abdul Aziz Shah Persiaran Usahawan, Seksyen U1, 40150 Shah Alam Selangor Telephone No. : 03 5163 4000 Fax No. : 03 5569 1903

PREFACE “Power comes not from knowledge kept, but from knowledge shared” -Bill Gates The objective of this book is to introduce students and readers to explore the theoretical and practical knowledge focusing on spreadsheet. A spreadsheet is a computer application for organization, analysis, and storage of data in tabular form. By using an attractive illustrations and an orderly step hopefully it may help the students and readers to learn interestingly and making the learning process enjoyable. We would like to acknowledge Pn.Nariman Binti Hj. Daud for their valuable suggestion and comments which are contribute to the completion of this book. Special thanks to our colleagues for their continuous support and suggestion throughout the development of this book.

TABLE OF CONTENT 1

Definition of Spreadsheet

Introduction to Excel

The Backstage

Create New Spreadsheet

Excel Screen

Worksheets Views

Working with Worksheets

Understanding Cells

Formulas and Functions

Transpose Table

LOOKUP Function

Chart or Graph

Print a Worksheet

Excel spreadsheet might as well be one of the most dangerous Rolf Dobeli recent ________________ _ inventions picturequotes.com

spreadsheet Topic 2 spreadsheet 1 Definition of Spreadsheet Spreadsheet is a computer application for organization, analysis, and storage of data in tabular for spreadsheets were developed as computerized analogs of paper accounting worksheets.

2 Introduction to Excel In Excel, a computerized spreadsheet is called a worksheet. The file used to store worksheets is called a workbook.

spreadsheet 3 The Backstage

The Backstage view gives you various options for saving, opening a file, printing, and sharing your document.

New New contains contains options options to to create create a new new Excel Excel file a file

Open Open allows allows you you to to open open a a file file from your local hard from your local hard disk disk or cloud cloud or

Info Info contains contains different different information information about about the the Excel file file Excel

Save Save & & Save Save As As use use "Save" "Save" & & "Save "Save As" As" menu to save menu to save file file in in the the cloud or hard disk cloud or hard disk

Print Print Manage Manage file file for for printing printing

Share Share allows allows you you to to share share file file using Microsoft Microsoft using OneDrive or OneDrive or Email Email

Export Export

Publish Publish

allows allows you you to to export export a a file file in PDF or XPS format in PDF or XPS format

allows allows you you to to publish publish the the file to Microsoft Power file to Microsoft Power BI BI

spreadsheet 4 Create New Spreadsheet

From the Excel Start Screen locate and select Blank Workbook to access the Excel interface. i.

Click the Start button on the Windows taskbar. - The Start menu opens

ii.

Point to Programs – - The Programs menu opens

iii.

Click Microsoft Excel - The Excel opens a new workbook

spreadsheet 5 Excel Screen

Formula Formula Bar Bar In In the the formula formula bar, bar, you you can enter / edit can enter / edit data, data, a a formula formula or or a a function function that that appear appear in in a a specific specific cell. cell.

Vertical Vertical & & Horizontal Horizontal Bars Bars The The scroll scroll bars bars allows allows you you to to scroll scroll up and down or side to side. up and down or side to side. To To do do this, this, click click and and drag drag the the vertical vertical or or horizontal scroll scroll bar. bar. horizontal

Worksheets Worksheets Excel Excel workbook workbook holds holds one one or or more more worksheets. Click on the worksheets. Click on the tabs tabs to to switch between them, or right-click switch between them, or right-click for for more more option. option.

Zoom Zoom Control Control Click Click & & drag drag the the slider slider to to use the zoom control. use the zoom control. The The number number on on the the right right of the slider reflects of the slider reflects the the zoom percentage. percentage. zoom

Ribbon Ribbon The The ribbon ribbon contains contains multiple tabs, multiple tabs, each each with with several several groups groups of of commands. commands.

spreadsheet Ribbon Tabs

File Tab

allows you to jump into the backstage view that contains the essential file-related commands and Excel options. This tab was introduced in Excel 2010 as the replacement for the Office button in Excel 2007 and the File menu in earlier versions.

Home tab

contains the most frequently used commands such as copying and pasting, sorting and filtering, formatting, etc.

Insert Tab

is used for adding different objects in a worksheet such as images, charts, PivotTables, hyperlinks, special symbols, equations, headers and footers.

Page Layout Tab

provides tools to manage the worksheet appearance, both on screen and printed. These tools control theme settings, gridlines, page margins, object aligning, and print area.

Formula Tab

contains tools for inserting functions, defining names and controlling the calculation options.

Data Tab

holds the commands for managing the worksheet data as well as connecting to external data.

Review Tab

allows to check spelling, track changes, add comments and notes, protect worksheets and workbooks.

View Tab

provides commands for switching between worksheet views, freezing panes, viewing and arranging multiple windows.

Help Tab

only appears in Excel 2019 and Office 365. This tab provides quick access to the Help Task Pane and allows you to contact Microsoft support, send feedback, suggest a feature, and get quick access to training videos.

spreadsheet 6 Worksheets Views

Normal View

Page Break View

Page Layout View

There are 3 views to display the workbook: • Normal view • Page Layout view • Page Break view

Normal view is the default view for all worksheets in Excel. Figure below is the example of normal view.

spreadsheet Page Layout view displays the appearance of printed sheet & can add headers and footers in this view.

Page Break view allows to change the location of page breaks. Helpful when printing a lot of data from Excel.

happines s is ….correctly applying a spreadsheet formula Rolf Dobeli ________________ _ picturequotes.com

spreadsheet 7 Working with Worksheets 7.1 Insert New Worksheets

3 ways to insert New Sheet ●

Click ‘+’ sign at the bottom of the sheet

●

Right-click on the existing sheet named as ‘Sheet1’ by default

●

Click on the ‘Insert’ icon available on the right side of the Ribbon bar, in order to open a drop-down list of options to be selected

spreadsheet 7.2 Delete a Worksheets

● Right-click the Sheet tab & select. Delete. ● Or, select the sheet, and then select Home> Delete> Delete Sheet.

7.3 Rename a Worksheets

●Double-click the sheet tab, and type the new name.

By By default, default, Excel Excel names names worksheets Sheet1, Sheet2, worksheets Sheet1, Sheet2, Sheet3 Sheet3 and and so so on, on, but but you you can easily rename them. can easily rename them.

●Right-click the sheet tab, click Rename, and type the new name. ●Use the keyboard shortcut Alt+H > O R, and type the new name.

spreadsheet 7.4 Moving and copying a Worksheets Method 1 Copy Excel sheet by dragging i.

Drag-and-drop to move something from one place to another.

ii.

click on the sheet tab that you want to copy, hold the Ctrl key and drag the tab where you want it

Method 2 Duplicate a sheet by right-clicking i.

Right click on the tab and select Move or Copy from the context menu. This will open the Move or Copy dialog box.

ii.

Under Before sheet, choose where you want to place the copy.

iii. Put a tick in the Create a copy box then click OK.

spreadsheet

Method 3 Copy a tab in Excel using the ribbon To copy a sheet, go to the Home tab > Cells group, click Format, and then click Move or Copy Sheet

8 Understanding Cells Every worksheet is made up of thousands of rectangles, which are called cells. A cell is the intersection of a row and a column. Columns are identified by letters (A, B, C), while rows are identified by numbers (1, 2, 3).

Cell

Column

Row

spreadsheet Each cell has its own name/address - based on its column and row. For example column C and row 5, so the cell name/address is C5. The cell name/address will also appear in the Name box.

Note that a cell’ s column and row headings are highlighted when the cell is selected. Cell address appears in Name Box

Multiple cells also can select at the same time (called as cell range). For example, a cell range that included cells A1, A2, A3, A4, and A5 would be written as A1:A5.

Cell range A1:A8

Cell range A1:B8

spreadsheet 2.8.1 Mouse Pointer

Several different types of mouse pointer as as follows.

Mouse Pointer

Image

Description

General Select

Selecting a range of cells by clicking and dragging the mouse over the cells.

Fill/Copy

Copying cell content or using the autofill function.

Move Cell (or range of cells)

Moving contents of a cell or range of cells. Click and drag to move cell contents.

Column/Row Resize

Column/Row Select

Making columns or rows wider or narrower. Click and drag to manually resize or double-click to resize to widest entry. Select a column or row. Single click to select the column or row. Click and drag across the headings to select multiple rows or columns.

spreadsheet 2.8.1 Mouse Pointer

Cell can contain different types of content,including text, formatting formulas, and functions.

Text Cells can contain text, such as letters, numbers, and dates.

Formulas and functions Cells can contain formulas and functions that calculate cell values.

Formatting attributes Cells can contain formatting attributes that change the way letters, numbers, and dates are displayed. For example, percentages can appear as 0.15 or 15%.

spreadsheet 2.8.2 Resizing and merge cell Every row and column of a new workbook is default set to the same height and width

AutoFit columns and rows with a double-click

Every row and column of a new workbook is default set to the same height and width

spreadsheet 2.8.3 Autofill

AutoFill is the properties in excel by which automatic fill next value in next cell.

Fill Series

To create auto fill we have to move cursor horizontally or vertically until release button.

There are some AutoFill that already defined by excel such as month, day, number etc. we can also create our custom AutoFill.

Copy Cells

spreadsheet 2.8.4 Sorting

Sorting is a common task that allows you to change or customize the order of your spreadsheet data

Sort Sort Click Click sort sort and and there there are are option to sort from option to sort from A A to to Z Z or or vice vice versa. versa. Here Here is is the the example example of of data that had data that had been been sort sort from from A A to to Z. Z.

spreadsheet 2.8.5 Copying and moving data COPY AND PASTE

• Select the cell(s) you want to copy • Click the Copy command on the Home tab, or press Ctrl+C on your keyboard. • Select the cell(s) where you want to paste the content. • Click the Paste command on the Home tab, or press Ctrl+V on your keyboard. The content will be pasted into the selected cells.

• Select the cell or cell range you want to cut.

CUT AND PASTE

• Click the Cut button on the Home tab. (Or Press Ctrl + X) • Click the cell where you want to paste your data. • Click the Paste button. (Or Press Ctrl + V)

Copy Cells

Paste Destination

spreadsheet 2.8.6 Insert or delete a row and column

● Select any cell within the row or column, then go to Home> Insert> Insert Sheet Rows /Columns ● or Delete Sheet Rows/Columns.

● Alternatively, right-click the row number or the column

spreadsheet 2.8.7 Common Error in Excel Error

Example

##### When your cell contains this error code, the column isn't wide enough to display the value.

#NAME? The #NAME? error occurs when Excel does not recognize text in a formula.

#VALUE! Excel displays the #VALUE! error when a formula has the wrong type of argument.

#DIV/0! Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

#REF! Excel displays the #REF! error when a formula refers to a cell that is not valid. 1. Cell C1 references cell A1 and cell B1.

KEEP CALM i have a spreadsh eet for that Anonymous ________________ _ goodreads.com

spreadsheet 2.9 Formulas and Functions A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel. All formulas must begin with an equals sign (=)

=SUM(A1:A20) Equal Sign

Function Name

Argument

Formula Bar Formula Result

2.9.1 Basic Function MATHEMATICS SYMBOL OPERATIONS ADD SUBTRACT MULTIPLY DIVIDE

+ * /

Name

Function

Sum

Total

Average

Average of data

Max

Largest value

Min

Smallest value

Count

The number of entries

Name

Function

spreadsheet Example

TABLE TABLE A A

Refer Refer to to the the Table Table A, A, calculation calculation use use function: function:

MATHEMATICS OPERATIONS

EXAMPLE

ANSWER

ADD SUBTRACT MULTIPLY

140 -30 3500

DIVIDE

=C3+C4 =C3-C5 =C3*C6 =C4/C7

FUNCTION

STATEMENT

ANSWER

1.64

TOTAL (SUM)

=SUM(C3:C7)

345

Highest Mark Lowest Mark Average Mark

=MAX(C3:C7) =MIN(C3:C7) =AVERAGE(C3:C7)

90 50 69

Number or student =COUNT(C3:C7)

5 25

spreadsheet 2.10 Transpose Table Transpose - Change row and column of the data

Step Step to to transposing transposing the the table table

1. Select table 2. Right click and copy 3. Go to other cell or worksheet 4. Choose Paste Special 5. Click checkbox Transpose and OK

spreadsheet 2.11 LOOKUP Function The LOOKUP Function is categorized under Excel Lookup and Reference functions. The function performs a rough match lookup either in a one-row or one-column range and returns the corresponding value from another one-row or one-column range.

Records in row

2.11.1 VLOOKUP Function

••

VLOOKUP VLOOKUP is is an an Excel Excel function function to to find find and and retrieve retrieve large large data data in in a a table table organized vertically. organized vertically.

• •

VLOOKUP VLOOKUP supports supports approximate approximate and and exact exact matching, matching, and and wildcards wildcards (* (* ?) ?) for partial matches. for partial matches.

• •

The The data data must must be be in in row row

• •

VLOOKUP VLOOKUP requires requires a a lookup lookup table table with with lookup lookup values values in in the the left-most left-most column column

spreadsheet VLOOKUP has two modes of matching: exact and approximate, which are controlled by the 4th argument, called "range_lookup". Set range_lookup to FALSE to force exact matching, and TRUE for approximate matching. Lookup formula

Lookup value Lookup result Formula Formula for for VLOOKUP VLOOKUP =VLOOKUP(value, =VLOOKUP(value, table, table, column, column, FALSE) FALSE) (for (for exact exact match) match)

VLOOKUP V IDEO

spreadsheet 2.11.2 HLOOKUP Function HLOOKUP is an Excel function to lookup and retrieve data from a specific row in table. The "H" in HLOOKUP stands for "horizontal", where lookup values appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.

Records in column Lookup formula 1 2 3 4

Lookup value Lookup result Formula Formula for for VLOOKUP VLOOKUP =HLOOKUP(value, =HLOOKUP(value, table, table, row, row, FALSE) FALSE) (for (for exact exact match) match)

VLOOKUP V IDEO 29

spreadsheet 2.12 Chart or Graph •

A chart is a tool you can use in Excel to communicate your data graphically.

•

Charts allow your audience to see the meaning behind the numbers, and they make showing comparisons and trends a lot easier. Excel has several different types of charts, allowing you to choose the one that best fits your data.

2.12.1 Creating a Chart or Graph i.

Select the cells you want to chart, including the column titles and row labels. These cells will be the source data for the chart. In our example, we'll select cells A1:F6.

spreadsheet ii. From the Insert tab, click the desired Chart command. We can choose from Recommended Charts or another chart types.

Recommended Charts

iii. Choose the desired chart type from the drop-down menu

CLICK Here - Video Creating Chart

spreadsheet 2.13 Print a Worksheet To print a worksheet in Excel, execute the following steps. 1. On the File tab, click Print. 2. To preview the other pages that will be printed, click 'Next Page' or 'Previous Page' at the bottom of the window Orientation

Preview Pane

can choose portrait or landscape orientation.

Paper Size

Aa preview of how your worksheets will look when printed.

Choose the paper size

Margins The smaller the margins, the more room there will be for your data

Show Margins / Zoom to Page

Page Selection Click the arrows to view a different page in the Preview pane.

The Zoom to Page button - will zoom in and out in the Preview pane. The Show Margins button - will show the margins in the Preview pane.

spreadsheet

spreadsheet 1. Apply the formatting as follow. 100

Currency

100

Percentage

100000

Thousands comma separator

100.0

Increase decimals

2. By using MAX, MIN AND SUM function, find the maximum, minimum and sum value within a range of cells. Building

Height (m)

Four Seasons Place KL

343

Vortex Tower

260

Petronas Twin Tower

452

Telekom Tower

310

Maybank Tower

244

3. Arrange the following people from the oldest to the youngest and calculate the average age. . Name

Age

Naurah

Nuha

Naufal

Umar

Aafiyah

Irsyad

spreadsheet 4. Open the new worksheet, and then a)

Enter the following data into cell address

Merge and Center the title ‘EXPENSE PLAN’ in the range A1:E1

Bold the range A3: E3, A4:A13 and B14:B16

By using suitable formula, find the value of Monthly Spend and Percent Change.

e) f)

AutoSum the Totals for cells B13 to E13. Use the Average function to find the value in cell C14 and D14

Use the Min function to find the value in cell C15 and D15

Use the Max function to find the value in cell C16 and D16

Format the number of price cells as a currency.

Format the column Percent Change as a percentage.

Create a pie chart of the Monthly Spend.

spreadsheet 5. Follow the instruction below. a)

Create a new worksheet and enter the following data.

Merge and Center A1 to the range A1:F1

Merge and Center A2 to the range A2:F2

Bold and Center A3:F3

Bold A4:A10

Calculate the sub-total in cell D4

Use the Fill Handle to copy the formula in cell D4 to the range D5:D10

Calculate the tax amount in cell E4

Use the Fill Handle to copy the formula in cell E4 to the range E5:E10

Use the Fill Handle to copy the formula in cell F4 to the range F5:F10

Apply the Light Blue Data Bar Conditional Formatting to the range F4:F10

Apply the Total Style to the range A12:F12

m) AutoSum the range F4:F10 in cell F12 n)

Use the Max function to calculate the Max value of F4:F10 in Cell F13

Use the Min function to calculate the Min value of F4:F10 in Cell F14

spreadsheet 6. Follow the instruction below. a)

Open a new spreadsheet file.

Create a table as given below.

With suitable formula, fill the color parts. (Refer the formula given)

Insert the institution logo.

Create a bar chart of the total marks of all students.

spreadsheet 7. Follow the instruction below. a)

Create a new worksheet and enter the following data.

b) Merge and Center the title “Market Shares of Major Phones Providers in the United States” in the range A1:G1 c)

Change the title font to Cambria 14 pt. Bold

d) Merge and Center the sub-title “Cellular Phones Sales During Five Years” in the range A2: G2 e) Change the sub-title font to Time New Roman, Bold f)

Bold the range A3:A13 and B3:G3

g) Apply Total style to the range A10:F10 h) Type =B4+B5+B6+B7+B8 in cell B10, then press enter i)

Use the AutoSum function to calculate the total in cell C10 for the range C4:C8

Use the Fill Handle to copy the function in cell C10 to the range D10:F10

AutoSum the range B4:F4 in cell G4

Use the Fill Handle to copy the function in cell G4 to the range G5:G8

m) AutoFit the contents of each column (Do not display #######) n)

Add the $ sign from the range B4:F8 and set the Decimal Places to 0

Use the Max function to display the highest value of the range B4:B8 in cell B11

Use the Min function to display the lowest value of the range B4:B8 in cell B12

Calculate the average of the range B4:B8 in cell B13

Select the range B11:B13 and use the Fill Handle to copy the functions in these cells to the range C11:G13

Delete row 9

spreadsheet References 1.

Introduction to Information Technology/Spreadsheet Wiki Book , retrieved 23 June 2021 from https://en.wikibooks.org/wiki/Introduction_to_Information_Technology/Spr eadsheets

2. ^ "spreadsheet". Merriam-Webster Online

Dictionary.

Retrieved 23

June 2016. 3.

Excel 2016- Getting Started with the Excwel retrieved 23 June 2021 from https://edu.gcfglobal.org/en/excel2016/getting-started-with-excel/1/

VLOOKUP retrieved 20 July 2021 from https://corporatefinanceinstitute.com/resources/excel/study/vlookup-guid e/

Create Chart From Start to Finish. Retrieved 15 Augudt 2021 from https://support.microsoft.com/en-us/office/create-a-chart-from-start-tofinish-0baf399e-dd61-4e18-8a73-b3fd5d5680c2