QUATTRO PRO SPREADSHEET

1.         A spreadsheet program should be used when dealing with many _______________ and _____________ or if you want to make a ____________________.

2.         a.         Spreadsheets are set up in columns and rows. ___________ indicate Columns and _______________ indicate Rows.

            b.         The intersection of a column and a row is called a ___________. C5 is example of a cell address.

3.         Quattro Screen and Tools

            a.         Label these parts: tool bar, property bar, input line (formula bar),

            b.         Circle and label these tools: justification, join and center, cell colour, cell lines, text colour, zoom.

4.         3 Types of Data

            a.         text (words) is called ______________. They will appear __________ aligned in the cell.

            b.         numbers are called ______________. They will appear __________ aligned in the cell.

            c.          formulas are called formulas. The answer will appear in the cell.

            d.         When entering numbers, DO NOT key a space after the thousands, eg. 39 465. If you do the number will seen as ____________. Why is this a problem?

________________________________________________________________

5.         If a column is not wide enough to fit data what happens:

            a)         to labels (text)

If the next cell is empty? ________________________

If the next cell contains data? _______________________

            b)         to numbers? ________________ appear, whether the next cell is empty or not.

            c)         If this happens ___________________________ and the data will appear.

6.         Formulas

            a.         When keying a formula start with a ________

            a.         The symbol for division is __________, and for multiplication is ________

7.         Functions - are special formulas used to speed up some calculations, eg.

            ___________    - adds all numbers in the range of cells indicated,

            ___________    - calculates the average of all numbers in the range of cells selected

            ___________    - gives the largest number in the range

            ___________    - gives the smallest number in the range

8.         To enter a formula key any ____________________ or _____________ and click on any ____________________. Press __________________ when done. Eg. for the formula =B3*6+B2, key ______, click on _________ key _____________, click on ___________, press enter.

            Entering formulas using Functions

                        a.         go to the cell where the answer is to appear

                        b.         Key the function up to the left bracket, eg. To add several cells key ________________

                        c.          Click and drag to select the cells to add together

                        d.         press ENTER when done.

9.         Copying a Formula to Other Cells

Click in the cell with the formula > Ctrl C or right-click copy > select cells to copy the formula into > Ctrl V or right-click > paste

Use your mouse to select from the cell that you entered the formula in down or across to where you want it copied > right-click > quick fill.

When you copy a formula into other cells, the cell references the formula change automatically. This is called Relative Cell Referencing.

10.       Editing Cells

To change a cell’s contents without rekeying the entire thing ______________ in the cell OR click in the ______________________ OR press the ____________ function key.

11.       Clearing Cells:

Edit Menu > Clear ...

            a.         Clear Cells - clears the ____________ AND _______________. You can press Sh Delete to do the same

            b.         Clear Values - clears data but not the ________________. Press DELETE to do the same.

            c.          Clear Format - clears all formats (alignment, join and centre, underline, etc) but leaves ___________.

12.       Some Quick Cursor Movements

            __________      -           to cell A1 on sheet A (from any worksheet)

            __________      -           To cell A1 of the current worksheet (sheet, tab)

            Pg Dn                -          one screen Down

            Pg Up                -          one screen Up

            Ctrl Rt Arrow      -          one screen Right

            Ctrl Left Arrow   -           one screen Left

            Ctrl Pg Dn         -           to the next sheet (tab)

            F5                     -           the "go to" key (then key the cell address)

13.       Selection Properties

Is a very powerful menu choice. To use it select the cells to change > RC (right-click) > Selection Properties OR Format Menu >Selection Properties...

List the 7 tabs shown in this window:

_________________, _________________, _____________________, ____________________, ________________, __________________, ___________________

14.       Printing –

            a.         Click on the Print tool then:

            b.         be sure Current Sheet is selected, click on Print Preview button -- add a footer for hand-in info and make changes to margins, orientations, gridlines, etc

            d.         Click on print tool in preview window to send to printer

            e.         Save the file to save print changes.

15.       Using Lines to Enhance Your Work

            a.         select cells then use the line tool. It looks like this _____________ (it’s beside the text colour tool – label it on your diagram of the screen).=

            b.         OR shade a row of cells then change its height (drag the bottom line of the row indicator).

            c.          To get rid of lines you added (but keep the data) - edit menu > clear > format

16.       Column Widths:

            a.         To change a column width or row height click and drag the line on the

______________________________________

For row height drag the BOTTOM of the line to change its height.

            b.         To change the Width of Several Columns select them > RC > Cell Properties > Row/Column...

17.       Inserting Several Rows or Columns:

Rather than insert one row or column at a time, select the number of rows or columns to insert (they will be inserted before the first one selected) / insert. The number of rows or columns selected will be the number of new ones inserted.

            Note:    When inserting rows or columns they will be inserted _______________ the current. If there are several selected the inserted row/column will be before the first selected.

18.       Calculating Percents of Something

Key the formula to calculate each. If you have trouble coming up with the formula try translating the English statement to Math.

            Eg.       a)         To calculate 4% of Cost (cell B5)

_____________________________________________________

                         b)         To calculate a discount of 10% of the Price (cell D8)

_____________________________________________________

                         c)         To calculate the New Salary if a raise of 6% of the old salary (cell G22) is given.

Think--I’m asking for the New Salary NOT the Raise!!

_____________________________________________________

19.       TEMPLATES (see Invoice 1 and 2 assignments)

A template is a blank form that is prepared for use over and over again. We made a title page template for our essays and letterhead templates for our business letters.

What is included in a template?

Include any text, numbers, formulas and formatting that will not change when used over and over, even in cells that

20.       SORT (Alphabetic or Numeric Order):

Select the range to sort. You will need to select all cells in each row that you want to sort, otherwise only the data in the selected columns move and your data is no longer correct, Eg. if you only select the first column, the items in that column will be sorted, but all other columns will stay the same.

_______________ Menu > ______________ > set your sort criteria by selecting the column that the data to sort by is in (you can sort by up to 5 criteria).

Note if you have a separate column for first and last name, whenever you are asked to sort by name you would automatically sort by __________________ subsort _____________ name.

________________________ order = a to z or low to high number
________________________ order = z to a or high to low number

21. CHARTS (GRAPHS)

To create the chart --
1. Select Data > Insert Menu > Chart.
2. From here you will be walked through several choices. The 1st choice is Current Sheet or Chart Window
  
  Chart Window puts the chart in a separate window called the “Objects Sheet”. From here you can copy it into your spreadsheet. To get to Chart Window click on the TO END button (i.e. the Speed Tab Button) (or View Menu > Objects) then to go back to your spreadsheet click on the TO START Button (or View Menu > Page)
  
  Current Sheet puts the graph with the spreadsheet; this is called a Floating Chart. It is also placed on the Objects Sheet. Be careful because if you delete your floating chart you have also deleted the copy on your objects sheet.
If your x and y-series are not side by side, select any data so you can create the chart then later edit your x and y series. Here's how -- Select data > Insert Menu > Chart. This creates the chart,
THEN
To make the necessary data changes--Right-click anywhere on the Chart > Source Data > Make the necessary changes to your Series.
You will always have only one x-series and can have up to 6 y-series. Your x-series will be labels. They can be text or time frames (years, months, etc) that ARE SET UP AS LABELS (i.e. they were entered with a quotation mark in front so that they ARE labels). Your y-series will be numbers.
If your x-series is a column your y-series will be columns. If your x-series is a row your y-series will be rows. The x and y-series must line up.
Your x-series MUST be text (labels). If they are numbers, eg years, you must set them to be seen as text, or they won't show up on your chart. Here's how -- select the numbers that you want seen as text > right-click > Selection Properties > Number Format > Text.
If the x-series data won’t fit on a bar graph, even after making the font smaller, you can have them display on more than one row –Click on the x-axis data so all are selected > right-click on the selection > X-Axis Properties > Labels--change "Display on 1 row".
If you have only one y-series you do NOT use a legend (you don’t need it so don’t use it). If you have more than one y-series you MUST use a legend to differentiate the series.
The type of graph to use depends on what is required. If you need a chart to:
1. compare - use a bar chart
2. show trends or progressions over time - use a line chart
  (Note: The “time" data will be your X series). If the “time” is numbers (eg. years), change them to text format or they won’t show up when used as your x-series – Select > right-click > Selection Properties > Number Format > text.
3. show a percentage of the total - use a pie chart.
4. To change the type of chart after it has been made right-click anywhere in the chart > Edit Chart > Use the Chart Type Tool (the first tool on the Chart Property Bar) to change the type.
Pie charts:
1. always have only one y-series.
2. NOT change your data to percent format first; select the raw data and the numbers will automatically be changed to percent, since by definition a pie chart shows percentage of a total.
3. Don’t select a total number as part of your y-series. It’ll do the total when the chart is created.
4. If you want to “explode” a piece of pie to make it stand out, click on the slice to select it > right-click on the slice > Series Properties > Type Options tab...
To make changes to the look of the chart, click on the part then make changes using the Chart Property Bar that pops up or double-click on the part and make the desired changes
To Set Legends
1. In chart window – RC on the 1^st y-series bar > Series Properties > Legend > Key the legend (what this series stands for) > Repeat for all other y-series. Note: for a floating chart click inside box first (gray border appears) then RC on the 1^st y-series, etc.
  (There is another way to set legends but it’s a pain so use the above method!)
2. To change the legend position – RC in the legend area (not on one legend box) > legend properties...
Data Labels

You may want to see the number that each bar or point represents. These are called DATA LABELS. To set them –

Click on the first set of bars then right-click on it >Series Properties > Data Labels > Make the changes> Repeat for each set of bars. Note: The number format of the label series can be changed.
Objects Sheet
1. What happens when you copy a chart from the Objects Sheet into your spreadsheet? Answer: A backup copy is placed in the objects screen so you’ll have 2 of them for the one chart.
2. In the Objects Sheet, if you make changes to this new backup copy will it automatically change the Floating Chart in your spreadsheet? YES!
3. In the Objects Sheet, if you make changes to the original of the 2 charts, will it automatically change the Floating Chart in your spreadsheet? NO