………...
INTRODUCTION TO SPREADSHEET TUTORIAL
Basic features of a spread sheet
Spreadsheet is a huge table with many rows and columns. Spreadsheets are generally used to analyze data, make projections based on the data, and graphical representation of data.
Learning outcome
At the end of the tutorial the learner will be able to have generally basics about an excel spreadsheet, what its comprised of, how it can be used to up speed work some of the few lessons will include.
How to open Microsoft excel?
Understanding the ribbon.
Customization of the excel environment (a). ribbon
(b). changing colour theme (c). formulas settings.
(d). proofing settings (e). save settings
Opening a spreadsheet
Entering data in a spreadsheet
Using a spreadsheet to add, subtract or multiply data automatically
Hiding and unhiding rows and columns
Filtering data
Auto fitting
Inserting a comment.
You can use a spread sheet to: Enter data.
Edit data. Format data.
Perform calculations on the data.
Draw charts and graphs based on the data.
How to Open Microsoft Excel?
Running Excel is not different from running any other Windows program. If you are running Windows with a GUI like (Windows 10, 8, and 7) follow the following steps.
Click on search button at the bottom of your computer screen.
Type in excel in the search space.
The computer will show the searched file on top.
Click on file to open it.
………...
Result:
………...
NB: A blank spreadsheet or work book will appear which you will use.
Understanding the Ribbon.
Ribbon is the name given to the row of tabs and buttons you see at the top of Excel.
The Ribbon is made up of the following components:
Tabs for each of Excel's main tasks that bring together and display all the commands commonly needed to perform that core task.
Groups that organize related command buttons into subtasks normally performed as part of the tab's larger core task.
Command buttons within each group that you select to perform a particular action or to open a gallery from which you can click a particular thumbnail - note that many command buttons on certain tabs of the Excel Ribbon are organized into mini-toolbars with related settings.
Dialog Box launcher in the lower-right corner of certain groups that opens a dialog box containing a bunch of additional options you can select.
………...
CUSTOMIZATION OF THE EXCEL ENVIRONMENT
a.Customization of the ribbon
You can easily create your own tab and add commands to it.
1. Right click anywhere on the ribbon, and then click Customize the Ribbon.
2. Click New Tab.
………...
3. Add the commands you like.
………...
4. Rename the tab and group.
………...
(b) changing the colour theme.
Click file on the ribbon of your excel.
Select options from the drop down menu. You should be able to see an Excel Options dialog window.
The general tab on the left-hand panel will be selected by default.
Look for colour scheme under General options for working with Excel
Click on the colour scheme drop-down list and select the desired colour
………...
Click on OK button (c) Setting formulas
This option allows you to define how Excel behaves when you are working with formulas. You can use it to set options i.e. autocomplete when entering formulas, change the cell referencing style and use numbers for both columns and rows and other options.
Steps
Click file on the ribbon of your excel.
Select options from the drop down menu. You should be able to see an Excel Options dialog window.
The select formulas.
………...
If you want to activate an option, click on its check box. If you want to deactivate an option, remove the mark from the checkbox. You can this option from the Options dialogue window under formulas tab from the left-hand side panel.
(d) Proofing settings
This option manipulates the entered text entered into excel. It allows setting options such as the dictionary language that should be used when checking for wrong spellings, suggestions from the dictionary, etc. You can this option from the options dialogue window under the proofing tab from the left-hand side panel.
………...
(e) save settings
This option allows you to define the default file format when saving files, enable auto recovery in case your computer goes off before you could save your work, etc. You can use this option from the Options dialogue window under save tab from the left-hand side panel.
………...
FEATURES OF A SPREEDSHEET
………...
COLUMNS. Columns are vertical (Each column has an alphabet that appears at the topmost position
ROWS. Rows are horizontal (Each row has a number that appears at the leftmost position).
CELL. A Cell is the intersection of a row with a column.
RANGE. A range in Excel is a collection of two or more cells.
Range Examples
A range is a collection of two or more cells.
1. To select the range B2:C4, click on cell B2 and drag it to cell C4.
How to drag a value in cells?
1a. Enter the value 2 into cell B2.
………...
1b. Select cell B2, click on the lower right corner of cell B2 and drag it down to cell B8.
Hide Columns or Rows/unhide
Sometimes it can be useful to hide columns or rows in Excel.
Hide
To hide a column, execute the following steps.
1. Select a column.
2. Right click, and then click Hide
………...
Result.
Note: to hide a row, select a row, right click, and then click Hide Unhide
To unhide a column, execute the following steps.
1. put curser where arrow is pointing in the illustration picture.
………...
2. Right click, and then click Unhide.
Result:
………...
Note: to unhide a row, select the rows on either side of the hidden row, right click, and then click Unhide
Copy/Paste a Range
To copy and paste a range, execute the following steps.
1. Select the range, right click, and then click Copy (or press CTRL + c).
2. Select the cell where you want the first cell of the range to appear, right click, and then click Paste under 'Paste Options:' (or press CTRL + v).
………...
Insert Row, Column
To insert a row between the values 20 and 40 below, execute the following steps.
1. Select row 3.
2. Right click, and then click Insert.
Result:
………...
The rows below the new row are shifted down. In a similar way, you can insert a column.
AutoFill
Use AutoFill in Excel to automatically fill a series of cells.
1. For example, enter the value 10 into cell A1 and the value 20 into cell A2.
2. Select cell A1 and cell A2 and drag the fill handle down. The fill handle is the little black box at the lower right of a selected cell or selected range of cells.
Note: AutoFill automatically fills in the numbers based on the pattern of the first two numbers.
You can repeat this using values of your own or months.
. Enter the time 6:00 AM into cell A1.
15. Select cell A1 and drag the fill handle across. AutoFill automatically fills in the times.
………...
Comments Insert comment.
1. Select a cell.
2. Right click, and then click Insert Comment.
Type your comment.
………...
Excel displays a green triangle in the upper-right corner of the cell.
2 Click outside the comment box if done.
Click in the cell with the red triangle to view the comment.
FILTERING DATA IN EXECL
This helps you choose out a preferred row among many that you require.
In our example we a looking for data of Raymond and William only in the spreadsheet.
Steps
Click in the top cell in the row of items you want in your spreadsheet.
Move your curser to the tool bar and click on data
Choose filter option.
………...
Press enter after choosing filter
An arrow will appear in your excel top cell which you clicked in.
Click on the arrow
Un tick all the ticked boxes by clicking select all.
………...
Then choose the data you want to appear.
In our case its William and Raymond’s data needed.
Result.
After pressing ok.
………...
AutoFit
You probably know how to change the width of a column in Excel, but do you know how to automatically fit the widest entry in a column?
AutoFit Column Width
The default width of a column is 64 pixels.
1. You can change the width of a column by clicking and dragging the right border of the column header.
2. To automatically fit the widest entry in a column, double click the right border of a column header.
………...
3. To auto fit multiple columns, first select multiple columns by clicking and dragging over the column headers
Note: to select non-adjacent columns, hold CTRL while clicking the column headers.
4. Next, double click the right border of one of the column headers.
Creating a new spread sheet
Click on the icon found down on the spreadsheet +
An option of new sheet will show then click yes.
………...
How to enter data
Click in the cell with the curse
Then enter information in chosen cell
RENAMING A SPREADSHEET.
Go down to the sheet name
Right click on your laptop and choose option rename to change
………...
USING SPREADSHEET FOR ADDITION, SUBTRACTION AND MULTIPLICATION.
We going to try and find out the total of James and Peters age by addition.
Click in an empty cell where you want your calculation and insert equal signs.
Click in the cell where James age is then press additional sign on your computer
Click in the cell where Peters age is too.
………...
Then after press the enter button on your computer to get the answer.
The total age of James and peter which is 15+22=37.
Try this out for the other formulas subtraction and addition using excel spreadsheet while following our steps only changing the formula used for practice.
………...
Calculation of totals using excel automatic total sum
Click in the cell below the figures you want to sum.
Move your curser to the right extreme corner of the sheet and click on the symbol ∑
Select sum to sum up the amounts4
After selecting sum then press enter.in our case the total age will be 82.
………...
Important Excel shortcuts
Ctrl + P used to open the print dialogue window.
Ctrl + N creates a new workbook.
Ctrl + S saves the current workbook.
Ctrl + C copy contents of current select.
Ctrl + V paste data from the clipboard.
SHIFT + F11 Creates a new worksheet.
F2 Check formula and cell range covered.
ASSESSMENT QUESTION
1.Use the data in the spreadsheet below to test your understanding.
………...
(a). Hide column C (b). unhide column C
(c). insert a Row and fill in the the following particulars. Name Caro, Age 15, Weight 60kg.
(d). use the filter data on your spreadsheet to get Dan and Sara’s Age and Weight only.
(e). rename your spreadsheet.
(f). Get the sum of both the Age and Weight of all Names using Auto sum.
(g). open a new spreadsheet.
(h) copy and paste the names to your new spread sheet
(I). Add Ben and Josh’s weight and then subtract it with Sara’s weight.
(J). Multiply Ben and Josh’s Age.0
(k). insert a comment in the cell of Sara’s Age.
………...
