CS5: Electronic Spreadsheets I

This unit explains Electronic Spreadsheets I

Introduction to Spreadsheets

Spreadsheets are computer applications used to store, analyze, organize and manipulate data in the rows and columns of a grid. The program operates by taking in data, which can be numbers or text, into the cells of tables.

If the data number, the program will compute it for you depending on the function you need to be completed. Microsoft Excel is currently the industry standard for spreadsheets and worksheets. It is the most used spreadsheet and is available for Windows, MacOS, Android, and iOS. Other programs used include Google sheets, a cloud web-based program, LibreOffice, and several more.

The jobs that were once done by accountants are now managed and filed by a computer program for reasons of efficiency and organization. Spreadsheets and computer programs used to optimize data have changed the world for business and data analysis.

THIS VIDEO EXPLAINS THE INTRODUCTION TO SPREADSHEETS

Working with Spreadsheets

If you have added a Spreadsheet, the Spreadsheet will appear on the layout and a node will appear in the Data Navigator with the name you assigned to the spreadsheet (Figure 11.2). Spreadsheets in FCS Express share much of the functionality included in Excel with the added benefit of live updating as statistics or data in your layout changes. A list of some of the common features in Excel that FCS Express will support are:

Array Formulas

 

Appearance Customization and Conditional Formatting

 

Built-in Operators and Functions

 

Formula Creation and AutoSum

 

Formatting Cells

 

Find and Replace

 

Comments

 

Sorting

Jump to:

Entering text or numeric data in the spreadsheet

 

Creating a formula in a spreadsheet

 

Using AutoSum

 

Referencing cells in a spreadsheet

 

Inserting a Token or Custom Token in a cell

 

Inserting Token Tables

 

Formatting Spreadsheet cells

 

Spreadsheet Cell Value Tokens

 

Analyzing Files from the Data List with Spreadsheets

 

o Spreadsheet Statistics Available from Data Files

 

The spreadsheet may be selected, de-selected, moved, re-sized, copied, and pasted just like any object in FCS Express.

A spreadsheet that has been copied and pasted will create a duplicate of itself. Any information that is entered in one of the copies will be updated in the additional copies. The copies will be visible in the Data Navigator as separate Data Grids/Spreadsheets under the same node, Multiple spreadsheets may exist in the same layout and will appear as separate nodes in the Data Navigator.

Spreadsheets in FCS Express behave very similarly to Microsoft Excel. Just as in Excel there are countless features and options that can be performed. We will cover a few of the basic operations and important points for working with spreadsheets in FCS Express below.

 

To enter text or numeric data in the spreadsheet:

 

1. Click on the cell in the spreadsheet.

 

2. Begin typing in the cell.

 

To create a formula in a spreadsheet:

 

1. Click on a cell in spreadsheet.

 

2. Enter an equals sign “=”.

 

3. Enter a formula, function, or operator to evaluate.

 

4. The cell will return the value of the information entered.

 

You may select a cell in the spreadsheet and click on the Spreadsheet tab→Formulas. Click on the formula or function to use from the menu and it will populate the chosen cell as a blank formula.

 

AutoSum:

Alternatively, you may select a range of cells and choose a command from the AutoSum drop down (Figure 11.4). The AutoSum command you have chosen will be calculated across the selected range of data and appear below the final cell in the data range.

 

Available AutoSum functions are:

 

Sum Sums all values within a given range.
Average Calculates the average for the values within a given range.
Count Numbers Counts the number of cells containing numbers within a given range. Excludes counting cells that contains text.
Min Finds the minimum value in a given range.
Max Find the maximum value in a given range.

 

To reference cells in a spreadsheet:

 

1. Click on a cell in a spreadsheet.

 

2. Enter an equals sign “=”.

 

3. Click on a different cell in the spreadsheet that contains data.

 

4. The original cell will display the data referenced from the second cell.

 

To insert a Token or Custom Token in a cell:

 

1. Right click in the cell.

 

2. Choose Insert Token.

 

3. Select a token to insert in the same manner as inserting a token into a text box.

 

Alternative 1: Drag and drop a statistic, plot, gate, or other FCS Express item from which tokens can be derived into a cell (see more about dragging and dropping tokens and token types)

 

The token value, or multiple token values, will appear in one or many cells.

 

Alternative 2:

  1. Click on cell in the spreadsheet.
  2. Click on the Spreadsheet tab→Layout tab→Tokens→Insert Token.
  3. Choose the token of your choice to insert in the cell.

 

Tokens that are present in cells will update in real time as data updates in plots. Cells that reference other cells or formulas containing token values with also calculate and update in real time. Please note that tokens can also be used elsewhere in FCS Express to reference spreadsheet cells.

 

To insert Token Tables:

 

Tables in FCS Express from Compensations, Signatures, SOPs, Canned Comments, and Heat Maps may be inserted into a spreadsheet by (Figure 11.5):

 

  1. Right clicking in a cell.
  2. Choose Insert.
  3. Select a token table to insert in the same manner as inserting a token into a text box.

THIS VIDEO EXPLAINS WORKING WITH SPREADSHEETS

Managing Spreadsheets

Excel is a Microsoft Office software program that provides worksheets and workbooks. Worksheets are documents comprised of rows, columns, and cells. In each cell the user can enter a number, date, text, math formula, or Excel function. Worksheets can also display selected data in one of a variety of chart types.

A workbook is just a collection of worksheets. When the Excel program is first opened, the user is presented with a workbook that contains three empty worksheets, also called spreadsheets. The first empty worksheet is displayed, and in the bottom left corner of Excel are three tabs – one for each worksheet – with the names Sheet1Sheet2, and Sheet3 as shown in the screenshot below. Arrows also display that allow the user to scroll right and left to locate worksheet tabs when a workbook has a large number of worksheets.

If you’re only using one worksheet, you don’t have to delete the two unused worksheets – most folks don’t bother. Excel workbooks are saved with a file extension of xlsx in newer versions of Excel. Older versions used the xls extension.

How many Excel worksheets can we have in one workbook? Microsoft says the number is limited to your computer’s memory! It’s handy to group together worksheets that are VERY closely related, and especially if you are linking data from one worksheet to another. But hopping back and forth using the worksheet tabs can become confusing.

Viewing, Renaming, Inserting, and Deleting Worksheets

“Sheet1” isn’t very descriptive. Here is how you view, rename, insert, and delete worksheets in a workbook.

How to View a Worksheet

To view a worksheet, click on its tab. If the workbook window is not wide enough to display all of the tabs because of long worksheet names and/or many worksheet tabs, use the arrows to the left of the tabs to navigate left or right, or right-click on any of the arrows and select the desired worksheet from the list that displays.

How to Rename a Worksheet

To rename a spreadsheet, right-click on the spreadsheet tab, select Rename from the context menu, and type a new name. Or, double-click on the worksheet tab and type a new name.

How to Insert a Worksheet

The fastest way to insert a worksheet in a workbook is to simply click on the small tab to the right of the last worksheet tab as shown in the image below. Then you can move the worksheet to a different position if desired.

Alternatively, you can insert a new worksheet to the left of an existing worksheet by right-clicking on the tab of the worksheet that is immediately to the right of where you want the new worksheet to be located and select Insert from the Insert window. Excel always inserts a spreadsheet to the left of the selected worksheet.

How to Delete a Worksheet

To delete a worksheet, right-click on the worksheet tab and select Delete from the context menu.

Moving Worksheets (Spreadsheets)

Sometimes we need our worksheets need to be in a different order or even in a different workbook.

How to Move a Worksheet in the Same Workbook

There are two ways to move a worksheet in the same workbook. The easy way is to click and hold the left mouse button on a worksheet’s tab and slide the tab to its desired position. Watch the little black arrow that appears just above. When it is to the right of left of the adjacent worksheet, release the mouse and the worksheet will be moved.

If you dislike dragging with the mouse, here is another method. Right-click on the tab of the source worksheet and click “Move or Copy…” In the Move or Copy window, click the name of the worksheet that you want the sheet to be inserted before, and click OK.

How to Move a Worksheet to a NEW Workbook

To move a spreadsheet to a new workbook, right-click on the tab of the source spreadsheet and click “Move or Copy.” In the Move or Copy window, click the drop-down arrow under “To Book:” and click (new book). Excel removes the worksheet from the existing workbook and opens a new workbook containing the moved worksheet. Save the workbook.

How to Move a Worksheet to a Different Workbook

Open both the source workbook and the target workbook. Right-click on the tab of the source worksheet (the one to be moved) and click “Move or Copy…” Then at the top under “To book,” click the small down arrow to open up the drop-down menu and click on the name of the target workbook (where the worksheet is to be moved to). Verify that the worksheet was successfully moved to the other workbook and save the workbook.

Copying Worksheets (Spreadsheets)

Rather than start from scratch, it is often easier to copy, and then modify, an existing worksheet – especially if you’re going to be using a lot of the same formatting, formulas, and so on.

How to Copy a Worksheet in the Same Workbook

To copy a worksheet in the same workbook, right-click on the tab of the source worksheet and click “Move or Copy…” In the Move or Copy window, check the “create a copy” box, click the name of the worksheet that you want the sheet to be inserted before, and click OK.

How to Copy a Worksheet to a NEW Workbook

To copy a worksheet into a new workbook, right-click on the tab of the source worksheet and click “Move or Copy…” In the Move or Copy window, click the drop-down arrow under “To Book:” and click (new book). Excel opens a new workbook containing the copied spreadsheet. Save the new workbook.

How to Copy a Worksheet to Different Workbook

The best way to copy a worksheet to another workbook is as follows: Open both the source workbook and the target workbook. Right-click on the tab of the source worksheet (the one to be copied) and click “Move or Copy…” On the Move or Copy window, CHECK the box at the bottom titled “Create a copy.” Then at the top under “To book,” click the small down arrow to open up the drop-down menu and click on the name of the target workbook (the other workbook). Verify that the worksheet was successfully copied to the other workbook and save the workbook.

As a rather messy alternative, you can copy and paste the contents as follows. In the source worksheet, right-click in the top left corner cell to select all the workbooks cells and select Copy.

Then, open the other Excel workbook, find an empty worksheet, right-click in the top left corner cell to select all cells, and click Paste. Save the workbook.

Return to the first (source) worksheet and press the ESC key to remove the animated border and then click in an empty cell to deselect all of the cells.

Formulas and Functions

Functions

A function uses a specific formula on an input to produce an output. They make it possible to do complicated math problems in spreadsheets without knowing the actual formula as functions are built into the software. For example, if you use a sum function for a column to find the total, all you would need to do is select all the cells you want to add and then use the SUM function.

These are also useful when working with large amounts of data. With functions a complex question of, “How much money does the average customer spend in my store?” could be accounted for by summing the total of all specified cells and dividing by the average amount of money.

Functions don’t always have to be the right way of working with an Excel document or any spreadsheet. Oftentimes, functions are used when compiling large amounts of data that creating a function takes less time than doing estimates by hand.

Formulas

Formulas used in spreadsheets, automatically process data how the user see fits. The formula takes data from certain areas in the spreadsheet, processes it, and places the output into the new area of the spreadsheet based on where the formula is written.

The formula can be as simple as “=SUM(A10,A11)” (which takes the information in the 10th and 11th cells of row A and outputs the sum), or as complex as the user wishes to make it. The functions used to create the formula (such as SUM), are predesignated by the spreadsheet software.

Features and Terminology

Spreadsheets have many features which help users visualize and manipulate data. This allows information to be processed faster and with more efficiency. Spreadsheets allow users to enter simple or complex formulas to perform automatic calculations on data in multiple cells. Spreadsheets can also perform dynamic updates, allowing users to generate data in one cell based on the values of others. Spreadsheet software gives users the ability to generate graphs and charts based on your inputted data.

When working with spreadsheets, one of the common terminology used is “cell”, without a cell, there cannot be a spreadsheet. In other words, we can say a spreadsheet is the arrangement of cells in rows and columns. A “cell” is a box where all data is inputted within a spreadsheet. A cell can be identified by the intersections of the rows and columns assigned to data that it represents, defined as the “cell address”. They are usually expressed in the format of, (column row). Examples of cell addresses can be A1,(column A row 1) B2(column B row 2),C3 (column C row 3), etc. The data imputed into a cell are usually texts, a numeric value, or a formula.

Microsoft Excel

Microsoft Excel is the most used spreadsheet software around the world.  The spreadsheets present tables arranged in rows and columns and are used to calculate basic and complex mathematical operations and functions. Along with the ability to handle complex mathematical operations, Excel features graphing tools, pivot tables, and also features a macro programming language called Visual Basic for Applications. The first version of Excel was released on September 30, 1985, for the Macintosh. The first Windows version wasn’t released until November of 1987.

Gnumeric

Gnumeric is an open source spreadsheet program that is part of GNOME free Software Desktop Project. Gnumeric version 1.0 was released on December 31, 2001. Its original intention was to replace other spreadsheet programs such as Microsoft Excel.

Excel is a Microsoft Office software program that provides worksheets and workbooks. Worksheets are documents comprised of rows, columns, and cells. In each cell the user can enter a number, date, text, math formula, or Excel function. Worksheets can also display selected data in one of a variety of chart types.

A workbook is just a collection of worksheets. When the Excel program is first opened, the user is presented with a workbook that contains three empty worksheets, also called spreadsheets. The first empty worksheet is displayed, and in the bottom left corner of Excel are three tabs – one for each worksheet – with the names Sheet1Sheet2, and Sheet3 as shown in the screenshot below. Arrows also display that allow the user to scroll right and left to locate worksheet tabs when a workbook has a large number of worksheets.

If you’re only using one worksheet, you don’t have to delete the two unused worksheets – most folks don’t bother. Excel workbooks are saved with a file extension of xlsx in newer versions of Excel. Older versions used the xls extension.

How many Excel worksheets can we have in one workbook? Microsoft says the number is limited to your computer’s memory! It’s handy to group together worksheets that are VERY closely related, and especially if you are linking data from one worksheet to another. But hopping back and forth using the worksheet tabs can become confusing.

Viewing, Renaming, Inserting, and Deleting Worksheets

“Sheet1” isn’t very descriptive. Here is how you view, rename, insert, and delete worksheets in a workbook.

How to View a Worksheet

To view a worksheet, click on its tab. If the workbook window is not wide enough to display all of the tabs because of long worksheet names and/or many worksheet tabs, use the arrows to the left of the tabs to navigate left or right, or right-click on any of the arrows and select the desired worksheet from the list that displays.

How to Rename a Worksheet

To rename a spreadsheet, right-click on the spreadsheet tab, select Rename from the context menu, and type a new name. Or, double-click on the worksheet tab and type a new name.

How to Insert a Worksheet

The fastest way to insert a worksheet in a workbook is to simply click on the small tab to the right of the last worksheet tab as shown in the image below. Then you can move the worksheet to a different position if desired.

Alternatively, you can insert a new worksheet to the left of an existing worksheet by right-clicking on the tab of the worksheet that is immediately to the right of where you want the new worksheet to be located and select Insert from the Insert window. Excel always inserts a spreadsheet to the left of the selected worksheet.

How to Delete a Worksheet

To delete a worksheet, right-click on the worksheet tab and select Delete from the context menu.

Moving Worksheets (Spreadsheets)

Sometimes we need our worksheets need to be in a different order or even in a different workbook.

How to Move a Worksheet in the Same Workbook

There are two ways to move a worksheet in the same workbook. The easy way is to click and hold the left mouse button on a worksheet’s tab and slide the tab to its desired position. Watch the little black arrow that appears just above. When it is to the right of left of the adjacent worksheet, release the mouse and the worksheet will be moved.

If you dislike dragging with the mouse, here is another method. Right-click on the tab of the source worksheet and click “Move or Copy…” In the Move or Copy window, click the name of the worksheet that you want the sheet to be inserted before, and click OK.

How to Move a Worksheet to a NEW Workbook

To move a spreadsheet to a new workbook, right-click on the tab of the source spreadsheet and click “Move or Copy.” In the Move or Copy window, click the drop-down arrow under “To Book:” and click (new book). Excel removes the worksheet from the existing workbook and opens a new workbook containing the moved worksheet. Save the workbook.

How to Move a Worksheet to a Different Workbook

Open both the source workbook and the target workbook. Right-click on the tab of the source worksheet (the one to be moved) and click “Move or Copy…” Then at the top under “To book,” click the small down arrow to open up the drop-down menu and click on the name of the target workbook (where the worksheet is to be moved to). Verify that the worksheet was successfully moved to the other workbook and save the workbook.

Copying Worksheets (Spreadsheets)

Rather than start from scratch, it is often easier to copy, and then modify, an existing worksheet – especially if you’re going to be using a lot of the same formatting, formulas, and so on.

How to Copy a Worksheet in the Same Workbook

To copy a worksheet in the same workbook, right-click on the tab of the source worksheet and click “Move or Copy…” In the Move or Copy window, check the “create a copy” box, click the name of the worksheet that you want the sheet to be inserted before, and click OK.

How to Copy a Worksheet to a NEW Workbook

To copy a worksheet into a new workbook, right-click on the tab of the source worksheet and click “Move or Copy…” In the Move or Copy window, click the drop-down arrow under “To Book:” and click (new book). Excel opens a new workbook containing the copied spreadsheet. Save the new workbook.

How to Copy a Worksheet to Different Workbook

The best way to copy a worksheet to another workbook is as follows: Open both the source workbook and the target workbook. Right-click on the tab of the source worksheet (the one to be copied) and click “Move or Copy…” On the Move or Copy window, CHECK the box at the bottom titled “Create a copy.” Then at the top under “To book,” click the small down arrow to open up the drop-down menu and click on the name of the target workbook (the other workbook). Verify that the worksheet was successfully copied to the other workbook and save the workbook.

As a rather messy alternative, you can copy and paste the contents as follows. In the source worksheet, right-click in the top left corner cell to select all the workbooks cells and select Copy.

Then, open the other Excel workbook, find an empty worksheet, right-click in the top left corner cell to select all cells, and click Paste. Save the workbook.

Return to the first (source) worksheet and press the ESC key to remove the animated border and then click in an empty cell to deselect all of the cells.

THIS VIDEO EXPLAINS MANAGING SPREADSHEETS

Formulas and Functions

Functions

A function uses a specific formula on an input to produce an output. They make it possible to do complicated math problems in spreadsheets without knowing the actual formula as functions are built into the software. For example, if you use a sum function for a column to find the total, all you would need to do is select all the cells you want to add and then use the SUM function.

These are also useful when working with large amounts of data. With functions a complex question of, “How much money does the average customer spend in my store?” could be accounted for by summing the total of all specified cells and dividing by the average amount of money.

Functions don’t always have to be the right way of working with an Excel document or any spreadsheet. Oftentimes, functions are used when compiling large amounts of data that creating a function takes less time than doing estimates by hand.

Formulas

Formulas used in spreadsheets, automatically process data how the user see fits. The formula takes data from certain areas in the spreadsheet, processes it, and places the output into the new area of the spreadsheet based on where the formula is written.

The formula can be as simple as “=SUM(A10,A11)” (which takes the information in the 10th and 11th cells of row A and outputs the sum), or as complex as the user wishes to make it. The functions used to create the formula (such as SUM), are predesignated by the spreadsheet software.

Features and Terminology

Spreadsheets have many features which help users visualize and manipulate data. This allows information to be processed faster and with more efficiency. Spreadsheets allow users to enter simple or complex formulas to perform automatic calculations on data in multiple cells. Spreadsheets can also perform dynamic updates, allowing users to generate data in one cell based on the values of others. Spreadsheet software gives users the ability to generate graphs and charts based on your inputted data.

When working with spreadsheets, one of the common terminology used is “cell”, without a cell, there cannot be a spreadsheet. In other words, we can say a spreadsheet is the arrangement of cells in rows and columns. A “cell” is a box where all data is inputted within a spreadsheet. A cell can be identified by the intersections of the rows and columns assigned to data that it represents, defined as the “cell address”. They are usually expressed in the format of, (column row). Examples of cell addresses can be A1,(column A row 1) B2(column B row 2),C3 (column C row 3), etc. The data imputed into a cell are usually texts, a numeric value, or a formula.

Microsoft Excel

Microsoft Excel is the most used spreadsheet software around the world.  The spreadsheets present tables arranged in rows and columns and are used to calculate basic and complex mathematical operations and functions. Along with the ability to handle complex mathematical operations, Excel features graphing tools, pivot tables, and also features a macro programming language called Visual Basic for Applications. The first version of Excel was released on September 30, 1985, for the Macintosh. The first Windows version wasn’t released until November of 1987.

Gnumeric

Gnumeric is an open source spreadsheet program that is part of GNOME free Software Desktop Project. Gnumeric version 1.0 was released on December 31, 2001. Its original intention was to replace other spreadsheet programs such as Microsoft Excel.

THIS VIDEO EXPLAINS FORMULAS AND FUNCTIONS OF SPREADSHEETS

ASSIGNMENT : CS5: Electronic Spreadsheets I Assignment MARKS : 10  DURATION : 1 week, 3 days

 

Welcome to FAWE

STEM Elearning

We at FAWE have built this platform to aid learners, trainers and mentors get practical help with content, an interactive platform and tools to power their teaching and learning of STEM subjects, more

How to find your voice as a woman in Africa

top
© FAWE, Powered by: Yaaka DN.
X