Microsoft Office/Create a Workbook to Analyze your Field of Interest with Embedded Chart
- 1 Vocabulary
- 2 Lesson
- 2.1 Save versus Save As
- 2.2 Workbook and Worksheet
- 2.3 Type in the following Table into Excel
- 2.4 General Data Analysis Questions
- 2.5 Worksheet Window
- 2.6 Adjust your column widths and row heights
- 2.7 Use AutoSum to compute the Totals row
- 2.8 Use the Fill Handle to smart copy with a relative reference
- 2.9 Use AutoSum to compute the totals column
- 2.10 Create a title with the Merge and Center
- 2.11 Format the document using Cell Styles
- 2.12 Format the numbers
- 2.13 Create a Column Chart
- 2.14 Formatting a Chart
- 3 Project
- 4 Rubric
Workbook - The Excel File that contains the group of spreadsheets
Worksheet - One sheet of the Excel File
Charts - The graphic display of the data (a graph such as a column, bar, line, or scatterplot)
Tables - The set of numbers and formulas with headings that represents the data
Sheet tab - at the bottom of the page is a set of tabs that takes you from one sheet in the workbook to the next
Column Heading - the letter above each column
Row Heading - the number at the beginning of each row
Cell - One rectangle in the spreadsheet, A1, C7. One place in a row and column. A basic unit of a worksheet into which you enter data.
Cell Reference - The name of the cell you are in - A15
Active Cell - The cell you are in and editing
Gridlines - The light lines that separate the cells
Normal View - The default view of a spreadsheet
Page View - The view of the document that lets you see how the pages would look.
Scroll bar - The bar to the right and at the bottom that lets you move around on the document window
Scroll Arrows - the arrows at the top and bottom of the scroll bar that let you move around the document window
Scroll Box - The box on the scroll bar that displays where you are in the document
Status Bar - Bar at the bottom of the window that shows information about the worksheet, the functions of the buttons the mouse is pointing to, or mode of excel you are in. If you right click on the status bar you can add and take away items that are displayed there. If you choose a range of cells it can tell useful information like the total and average. Check out what all is available.
- READY - Excel is ready to accept data
- ENTER - Excel is processing data
Ribbon - The list of commands across the top of the screen. They are different depending on the tab you are on.
Tab - The sets of commands that are listed across the top of the screen. When you click on them it changes the available commands in the ribbon.
Group - In the ribbon the commands are in sections called groups. There is a group for formatting text, one for page set up, and many others.
Home Tab - The default tab that opens when you open Excel. It has the most commonly used commands.
Active Tab - The tab you are currently using. Its commands are what are displayed in the ribbon.
Contextual Tabs - Additional tabs that are added to the ribbon when certain tasks are performed. If you enter a chart you will see the Chart Tools Tab and a new contextual tab DESIGN will be added to the ribbon.
Gallery - a graphic list of commands such as styles of charts.
Font - How characters are displayed
- Font Type - the appearance and shape of the letters
- Font Style - How the characters are emphasized (bold, underline, italic, ...)
- Font Size - The size of the characters on the screen
- Point Size - How fonts are measured - the size number corresponds to 1/72 of an inch. So size 72 would be 1 inch and size 36 would be half an inch
- Font Color - The color of the characters
Live Preview - When you are hovered over an item in a gallery you will see the effects live in your document. Those affects are not permanent until you click on the gallery item.
Formula Bar - the bar across the top that begins with Fx. This will show you the formula, function or contents of the active cell.
Name Box - in the top left, it give the cell reference, or name. You can navigate to different cells by typing a cell name and pressing enter.
Number - Excel allows you to format numbers to some pre-set number formatting
- General - a number that has by default one decimal place and no commas
- Number - You can set how negative numbers are displayed, add commas, and set the number of decimal places
- Currency - You can set how negative numbers are displayed, there will be commas and two decimal places with a floating dollar sign.
- Accounting - Negative numbers are in parenthesis, You can set the number of decimal places, there is a fixed dollar sign.
AutoSum - A function in Excel that allows you with one click to add up all the numbers in a row or column. It looks like a sigma.
Range - the set of cells you have choosen. If you click and drag from A1 down to C4 then the range is A1:C4 and all of those cells will be shaded.
Relative Reference - When you use the copy or fill handle to copy Excel adjusts the formula for the correct row or column. The adjustments are making a relative reference for the formula or function.
Format - Changing fonts borders and colors to emphasis different parts of the worksheet and make it easier to read.
Cell Styles - From the HOME tab you can clickon Cell Styles to pick predefined formatting that fits your theme for standard formats such as:
- Red means bad or negative
- Green means good or positive
- Yellow means neutral
- Italic for explanatory notes
- Accent colors that fit your theme
Merge - To take multiple cells and make them one. This is commonly used for titles across the top of a table. There is a merge and center button that can be used for this purpose.
Embedded Chart - A chart of the table of data that is on the same sheet as the data.
Portrait - When your paper is taller than it is wide.
Landscape - When your paper is wider than it is tall.
Fill Handle - The little black square in the lower right corner of a cell that allows you to smart copy the contents of the cell across a row or down a column.
Floating Dollar Sign - The dollar sign that appears immediately to the left of the first digit in the cell, with no spaces.
Fixed Dollar Sign - The dollar sign that always appears to the far left of the cell no matter how wide the cell is.
http://www.yacapaca.com EX1 - Career Analysis
Save versus Save As
When you want to save a document for the first time you want to click on SAVE. You can name the document, and tell the document where to save (what folder). Also if you have already saved the document and just want to make a change and save it back to the same place, then you would use SAVE.
When you already have a document and want to make copy of it then you would use SAVE AS. You can put the copy in the same folder, or in a different one. You should rename the file to remove any confusion between the original one and the copy.
Workbook and Worksheet
When you open an excel workbook it opens by default with 3 work sheets. You can see the three worksheets at the bottom of the screen as tabs: Sheet1, Sheet2, and Sheet 3. You can rename these tab, delete them, create more, and change the color of them. Information from the three tabs can interact if you use global names. (global names will be coverd in a later lesson)
Type in the following Table into Excel
Data collected from The Movie Times
|1||Gross Movie Sales|
|2||Top 10 Movies|
|4||WHERE THE WILD THINGS ARE||$32,625,407||$20,863,574||$9,091,398||$4,177,000||$2,418,000|
|5||LAW ABIDING CITIZEN||$21,039,502||$18,969,201||$11,476,577||$6,004,000||$3,796,000|
|9||CLOUDY WITH A CHANCE OF MEATBALLS||$108,201,645||$6,653,232||$3,749,201|
|10||Cirque du Freak: The Vampire's Assistant||$6,293,205||$4,516,770|
|11||Michael Jackson's this Is It||$34,442,926||$13,158,000||$5,079,000|
General Data Analysis Questions
- Why do some of the movies have such a high value in the first week on the chart?
- Why do some of the movies not have a value in the first week on the chart?
- Why do you think some of the movies drop off in value quickly as the weeks move on?
- What do the totals in column G represent?
- What do the totals in row 13 represent?
Same as Word
You will notice that many of the parts of the EXCEL Window are the same as the WORD Window. The right side and the top have scroll bars, with scroll arrows and scroll boxes. The top of the window has the Ribbon with the Tabs and Groups. Many of the Groups on the HOME tab are the same such as Font and Alignment. But some are different such as Number and Cells. The status bar is still at the bottom and gives information on the document as well as the active cell or range of cells you have chosen. Excel still has the Key Tip Badges, Enhanced Screen Tips, Screen Tips, Dialog Box Launcher and Task Panes. The menus for Mini Toolbar, Shortcut Menu, and Quick Access Toolbar are still available as well.
Different from Word
The Excel document contains different sheets, where as the Word document contains different pages.
In Excel you have column headings and row headings to display a grid that you can name the active cell or range of cells you are working with. The column headings are letters like A, B, C. The row headings are numbers like 1, 2, 3.
Each place that a row and a column intersect is called a CELL. The Active Cell is the cell you are currently editing, or have chosen. The name of the cell is something like: A1, B12, or F92, and this is also called the cell reference.
Dividing each cell is a faint line called a gridline. These lines do not print by default, but guide you to help you better see what cell, row, or column you are in.
In the upper left corner is the Name Box and it will display the name of the cell you have active. You can navigate to a specific cell by typing the name of the cell and pressing enter.
Next to the Name Box is the Function Box. This contains the actual contents of the active cell. If you have a formula then it will display that formula there. If you have a number of text that is what will be displayed there.
Adjust your column widths and row heights
- Column Widths
- Place your mouse between the column headings (the letters such as A and B)
- click and drag your mouse to the right to increase the column width
- click and drag your mouse to the left to decrease the column width
- double click to make the column width the BEST FIT for your data
- Row Heights
- Place your mouse between the row headings (the numbers such as 1 and 2)
- click and drag your mouse to down to increase the row height
- click and drag your mouse to the up to decrease the row height
- double click to make the row height the BEST FIT for your data
Use AutoSum to compute the Totals row
- Make the cell B13 active by clicking on it. This should be the first cell in the totals row.
- Click on the AutoSum button in the HOME tab in the EDITING group. (It looks like a sigma or a fancy E)
- Make sure the contents of the cell reads: =SUM(B4:B12), if it does not just click and drag over B4 to B12 and then press enter. Notice that the numbers are different.
- Do the same steps in cell C13 making sure the contents reads =SUM(C4:C12)and press enter
Use the Fill Handle to smart copy with a relative reference
- Click on cell C13
- Place your mouse over the tiny black box in the bottom left of that cell. This is the fill handle. When your mouse is on the fill handle it turns into a black plus sign.
- Click and drag the fill handle over to the right to cell G13
Use AutoSum to compute the totals column
- Click on cell G4 to make it active
- Click on the AutoSum button in the HOME tab in the EDITING group.
- Make sure it reads =SUM(B4:F4) Notice that now the letters are different and the numbers are the same.
- Do the same in cell G5
- Do the same in cell G6 - notice that it does not do it right and you will need to click on B6 and drag to F6
- Use the Fill Handle and drag it down to fill the rest of the column.
Create a title with the Merge and Center
- Click and drag from A1 to G1
- Click on the merge and center button in the HOME Tab in the ALIGNMENT group.
- This will take all of those cells and merge them into one cell called A1.
- Click and drag from A2 to G2
- Click on the merge and center button
Format the document using Cell Styles
- Format the titles
- Click on cell A1
- Click on the Cell Styles button in the HOME tab in the STYLES group.
- Choose TITLE for the style
- Click on cell A2
- Click on the Cell Styles button
- Click on the HEADING 4 style
- Format the headings
- Click and drag to choose the range A3:G3
- Click on Cell Styles button
- Click on HEADING3
- Format the totals
- Click and drag to choose the range A13:G13
- Click on Cell Styles button
- click on TOTAL style
Format the numbers
- Format the top row of dollars for standard accounting style
- Click and drag to choose the range B4:G4
- Click on the $ in the HOME tab in the NUMBER group - this applies the Accounting number style with a fixed dollar sign.
- Format the rest of the block of dollars for standard accounting style
- Select the range B5:G12
- Click on the Comma in the HOME tab in the NUMBER group
- Format the totals for accounting style
- Select the range B13:G13
- Click on the $ for accounting style
Create a Column Chart
NOTE: An EMBEDDED chart is one that is on the same sheet as the data table it is graphically displaying.
- Select the range A3:F12 - notice that we do not select the TOTALS
- Discuss why you would not select the totals for a column chart
- Click on the INSERT tab
- Click on the COLUMN button in the CHARTS group
- Choose a chart style from the gallery
- Discuss why you would want one from the first or fourth columns and what the ones in the second and third columns represent.
- the second column in the gallery add the data together and give one column per group
- the third column in the gallery gives a percent of the group for one column
- Click and drag the chart below your table of data so you can see both
- Make sure your legend is filled in with your row labels, if it is not, then you did not choose them when creating the chart
- Make sure your X-axis has labels, if not you did not choose row 3 (the column labels)when you created the chart
Formatting a Chart
- Click on the CHART TOOLS button at the very top of your window
- Click on the DESIGN tab
- Click on the Gallery More Button
- Choose a style for your chart, hover over different ones and look at the live preview before you select one
- Select a style that matches the title colors you created earlier
Create a Worksheet that compares the Job Outlook and Earnings for your top five occupationsyou selected with the career surveys.
Remember you created these from the web site http://www.collegeincolorado.org
Use the web site http://www.bls.gov/oco
The work sheet should have:
- Word Art Title at the top
- Columns for
- Job Title
- Employment in 2006
- Projected Employment in 2016
- Change in number
- Percent Change
- Lowest Earnings
- Highest Earnings
- Create a column chart that compares employment rates between 2006 and 2016.
- Create a second chart of your choosing (that is appropriate for the data) that compares high and low earning rates.
- Printed on one piece of paper
|Standards||1 point||2 points||3 points||4 points||5 points|
|WordArt Title||Has a title||Has a title for the topic||Explains the topic of the table or charts||Explains the topic of the table and charts||Completely explains the topic of the table and charts|
|Lists 5 careers under the title column||Lists 1 career||Lists 2 careers||Lists 3 careers||Lists 4 careers||Lists 5 careers|
|Column for employment rate in 2006||Missing 4 or more items||Missing 3 items||Missing 2 items||Missing 1 item||Filled in information for all five careers|
|Column for employment rate in 2016||Missing 4 or more items||Missing 3 items||Missing 2 items||Missing 1 item||Filled in information for all five careers|
|Column for change in number of job openings||Missing 4 or more items||Missing 3 items||Missing 2 items||Missing 1 item||Filled in information for all five careers|
|Column for Percent change in job openings||Missing 4 or more items||Missing 3 items||Missing 2 items||Missing 1 item||Filled in information for all five careers|
|Column for lowest earnings in career||Missing 4 or more items||Missing 3 items||Missing 2 items||Missing 1 item||Filled in information for all five careers|
|Column for highest earning in career||Missing 4 or more items||Missing 3 items||Missing 2 items||Missing 1 item||Filled in information for all five careers|
|Projection Chart||Missing 4 or more items||Missing 3 items||Missing 2 items||Missing 1 item||Includes x and y axis lables, includes a key, contains all required information|
|Earnings Chart||Missing 4 or more items||Missing 3 items||Missing 2 items||Missing 1 item||Includes x and y axis lables, includes a key, contains all required information|