CIS 50: Computing and Information Technology



ExcelAt the end of this assignment, you will:Create a spreadsheet using numbers, text, formulas, functionsCreate a chart from spreadsheet dataTHERE WILL BE TWO (2) PRINTOUTS/FILES TO SUBMIT FOR CREDIT:Assignment#1: — your_name_expenses.xlsxCreate new spreadsheet, enter text, numbers, formulas, functions, improve worksheet appearance, create a chart, save the workbook Grade: 10 points: 1/3 point for each correct formula (18 formulas), 4 points for correct chart (select specified cells, change chart title, add legends, add data series)Assignment#2: — your_name_fundraiser.xlsxCreate new spreadsheet, enter appropriate formulas, save the workbook. Grade: 10 points: When I receive your spreadsheet, I will change the number in cell B6 to 100, ALL cells must recalculate. There should be 23 formulas, 1/4 point for each correct formula. Formatting: 4 points.Expenses: The Expenses spreadsheet has simple formulas. The chart in expenses has you select non-adjacent or non-contiguous cells. A problem arises when trying to chart those selected cells. Microsoft free apps cannot do charts on non-adjacent or non-contiguous cells. Suggest that you use Microsoft Office 2019 desktop or, use Google sheets. I have not tried this spreadsheet in Microsoft 365Fundraiser: The Fundraiser spreadsheet has simple formulas and formatting. You can use Microsoft Office 2019 desktop, Microsoft Office online free apps, Google sheets, or Microsoft 365 (formerly Office 365)Submit via CanvasInformation: What is Excel, spreadsheet, worksheet, workbookMicrosoft EXCEL is a powerful electronic spreadsheet program that allows users to use worksheets to organize data, complete calculations, make decisions, graph data, develop professional looking reports, publish organized data to the Web.A spreadsheet is the computer equivalent of a paper ledger sheet, most commonly used to perform numerical calculations rapidly and accurately. It consists of a worksheet grid made from columns and rows. It is an environment that can make number manipulation easy and somewhat painless.EXCEL -- "Spreadsheets" vs. "Worksheets" vs “Workbooks”: Did you ever wonder if you should refer to a sheet within a MS Excel file as a "spreadsheet" or as a "worksheet"? You've probably heard and perhaps even used both terms. MS Excel is considered a spreadsheet program and sheets within an Excel file are referred to as worksheets. In other words, MS Excel is a spreadsheet program that produces worksheets but not a worksheet program that produces spreadsheets. Your collection of worksheets is saved as one workbook file. A "workbook" is a Microsoft Excel file. Each workbook can hold many "worksheets" (individual spreadsheets).The math that goes on behind the scenes on the paper ledger can be overwhelming. If you change the loan amount, you will have to start the math all over again (from scratch). But let's take a closer look at the computer version. Looking at our previous example it seems pretty evenly matched. Right? WRONG! The nice thing about using a computer and worksheet is that you can experiment with numbers without having to RE-DO all the calculations. Let’s change the interest rate and then the number of months. Let the COMPUTER do the calculations! Once we have the formulas setup, we can change the variables that are called from the formula and watch the changes. Do that on paper and you better get your calculator back out and get an eraser and hope you punched all the right keys and in the right order. Worksheets are instantly updated if one of the entries is changed. NO erasers! NO new formulas! NO calculators! Spreadsheets can be very valuable tools in business. They are often used to play out a series of what-if scenarios! (much like our car purchase here.) A worksheet stores information in columns and rows, electronically duplicating an accountant's ledger, a pencil, an eraser, and a calculator. With the worksheet, you enter numbers and text by typing on a keyboard, rather than writing with a pencil, and you view the results on a computer screen. Once you enter data in the worksheet, you can apply a variety of calculations - from simple addition, subtraction, multiplication and division to trigonometric, statistical and business calculations. With the worksheet, you can prepare such things as: budgets, income statements, tax statements, checking account balances, etc. With EXCEL, data is stored in the worksheet at the intersections of columns and rows called cells. Spreadsheet programs perform calculations on the numbers stored in the cells within the worksheet. Each cell can contain one of four types of information: a label (or text), a number, a formula, or a function. When you change data in your worksheet, EXCEL immediately recalculates any rmation: Anatomy of the MS Excel Window1. Start MS Excel program. Students can use Office 2019 desktop or Office 365. Office free online app does not have chart creating capability (not recommended for Expenses, OK for Fundraiser). Google sheets is another viable option.414337540005002. Open a new blank workbookWhen you launch Excel, the worksheet window displays the ribbon bar. This window ribbon bar contains tools that enable you to create and edit worksheets. Similar to Microsoft Word, the worksheet window contains a Title Bar, Microsoft Office button, Quick Access toolbar and tabbed Ribbon. In addition, below the Ribbons, you will see the Formula Bar. The Formula Bar can be used to enter and edit worksheet data.Microsoft Excel ribbon?is the row of tabs and icons at the top of the Excel window that allows you to quickly find, understand and use commands for completing a certain task. It looks like a kind of complex toolbar, which it actually is.The ribbon in Excel is made up of four basic components: tabs, groups, dialog launchers, and command buttons.Ribbon tab?contains multiple commands logically sub-divided into groups.Ribbon group?is a set of closely related commands normally performed as part of a larger task.Dialog launcher?is a small arrow in the lower-right corner of a group that brings up more related commands. Dialog launchers appear in groups that contain more commands than available mand button?is the button you click to perform a particular action.Ribbon tabsThe standard Excel ribbon contains the following tabs, from left to right:File?– 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?– contains the most frequently used commands such as copying and pasting, sorting and filtering, formatting, etc.Insert?– is used for adding different objects in a worksheet such as images, charts, PivotTables, hyperlinks, special symbols, equations, headers and footers.Draw?– depending on the device type you're using, it lets you draw with a digital pen, mouse, or finger. This tab is available in Excel 2013 and later, but like the?Developer tab?it is not visible by default.Page Layout?– provides tools to manage the worksheet appearance, both onscreen and printed. These tools control theme settings, gridlines, page margins, object aligning, and print area.Formulas?– contains tools for inserting functions, defining names and controlling the calculation options.Data?– holds the commands for managing the worksheet data as well as connecting to external data.Review?– allows you to check spelling, track changes, add comments and notes, protect worksheets and workbooks.View?– provides commands for switching between worksheet views, freezing panes, viewing and arranging multiple windows.Help?– 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.Developer?– provides access to advanced features such as VBA macros, ActiveX and Form controls and XML commands. This tab is hidden by default and you have to enable it first.Add-ins?– appears only when you open an older workbook or load an add-in that customizes the toolbars or menu.The worksheet window is divided into rows and columns. The columns are labeled alphabetically. The column headings (A, B, C…) display immediately below the Formula Bar. Rows are labeled numerically. There are 18,278 columns and 1,048,576 rows available in a worksheet. The intersection of a column and a row is called a cell. Cells can contain labels (text), values (numbers), formulas or functions (preprogrammed formulas). Cells are identified by their column letter and row number. For example, the first cell in a worksheet is referred to as A1. Google question: How many columns in Excel??Google question: How many rows in Excel??Math question: How many cells in Excel??Google question: How many sheets in Excel??At the bottom of the worksheet window, you will find sheet tabs. The sheet tabs enable you to enter data on multiple worksheets and store all of the worksheets together in one file called a workbook. You can have a maximum of ______ (Google question) worksheets in a workbook. In Excel, a cell must be selected before you start typing the data or formula. To select a cell, move the mouse pointer to the desired cell and click once. A dark border, called a cell pointer, will appear around the selected cell. When information is typed, the information will appear in the cell and in the Formula bar. After entering the data or formula, press the Enter, Tab or any arrow key to confirm the entry and move to an adjoining cell using the keyboard arrow keys.You will see an empty worksheet screen. EXCEL is waiting for you to enter data.Columns: labeled with lettersRows: labeled with numbersCell: intersection of column and row. Cells are the storage area of the spreadsheetCell pointer: indicates current cell. Cell address is indicated at the upper left corner of the screen. A1 is the current active rmation: text. numbers, functions, formulas – order of operationsEnter numbers, text, formulas, and functions: You can key one of four types of information into each cell: SYMBOL 129 \f "Wingdings" numbers/values - any number that is used for calculationsSYMBOL 130 \f "Wingdings" text/labels - any letter , digits, special characters or text; used for column titlesSYMBOL 131 \f "Wingdings" formulas - any number, cell reference and arithmetic operator (* / + -) must begin with an = sign; for example, =1+2*3, =A1+b1*100SYMBOL 132 \f "Wingdings" functions - EXCEL preprogrammed formulas, =SUM(a1:b3)Excel Basic Math Function and Formulas: Spreadsheets have many Math functions built into them. Of the most basic operations are the standard multiply, divide, add and subtract. These operations follow the order of operations (just like algebra). Let's look at some examples. For these following examples let’s consider the following data: A1 (column A, row 1) = 5 A2 (column A, row 2) = 7 A3 (column A, row 3) = 8 B1 (column B, row 1) = 3 B2 (column B, row 2) = 4 B3 (column B, row 3) = 6 AB153274386OperationSymbolConstant DataReferenced DataAnswerMultiplication*= 5 * 6= A1 * B330Division/= 8 / 4= A3 / B22Addition+= 4 + 7= B2 + A211Subtraction-= 8 - 3= A3 - B15= 3 + 4 * 6= B1 + B2 * B342 OR 27??Fun with Formulas: You can create mathematical formulas with the following arithmetic symbols. + add- subtract* multiply/ divideFormulas are important in Excel. Formulas are important in this assignment. You type in text for headings, numbers when you have to, but you create formulas and function to make Excel do the calculations. You should not use a calculator!! See the websites for more about formulas: HYPERLINK "" \t "_blank" #1: expenses.xlsxCreate a New SpreadsheetTo review what you have learned, you will create a new spreadsheet. Create the following spreadsheet. Remember, enter formulas in all the cells for: TOTAL column, Total Income row, Total Expenses row, and Balance row. Type your name in cell A25, type today's date in cell A26.You need to widen columns to accommodate the text.Save your spreadsheet with the filename: yourname_EXPENSES.xlsxIf you see ********* in a cell, it means that the cell is too narrow to display the value of the cell. A cell is only 9 spaces wide, but the words are too long for the column. Widen the column.Save your workbook again, use the filename: yourname_EXPENSES.xlsxWhen completed, your spreadsheet will NOT look EXACTLY like the text; it will have numbers where you have entered formulas. Are your formulas correct?? Change the numbers in cell b7, c7, d7 to 10000.00 Cells should recalculate: E7, B10, C10, D10, B23, C23, D23, F23. CELLS SHOULD RECALCULATE!! IF your spreadsheet did not recalculate, then you need to check the cells for formulasChange the numbers in cell b7, c7, d7 back to 3200 Charts and GraphsEXCEL, an electronic spreadsheet, is a software program that performs mathematical calculations and “what if” analysis. It replaces your pencil and calculator for solving financial and statistical problems. In addition, Excel can display numbers in the form of charts and graphs for easier reading and interpretation.A chart is a pictorial representation of the data entered in a worksheet. Numbers from the worksheet cells are displayed as bars, lines, columns, or pie slices. Showing your data in a chart can make it clearer to understand, more interesting, and easier to read and interpret. Charts of the numbers in your worksheet can also help you evaluate your data and make comparisons between different sets of numbers. Excel has 60+ pre-defined formats for charts and graphs. You are going to chart and graph the data you entered into your worksheet.View a tutorial on creating Excel graphs, go to the website: your your_name_Expenses.xlsx workbook; 18802354504055Your worksheet will have formulas in ALL the appropriate cells00Your worksheet will have formulas in ALL the appropriate cellsAfter you have successfully input ALL the formulas in the cells, you will select certain cells to chart.First, highlight ONLY the cells to be included into the chart; A13 thru A19 AND E13 thru E19Move the mouse pointer to cell A13, click and HOLD the left mouse buttonDrag the mouse pointer to cell A19, the area should be highlightedHOLD down the CTRL key, move the mouse pointer to cell E13 click and HOLD the left mouse button, Drag the mouse pointer to cell E19, the area should be highlightedRelease the mouse button, release the CTRL button. Both cells A13 thru A19 and E13 thru E19 should be highlighted. If not, try selecting again.You have selected non-adjacent cells. MAC users should use the command key. MAC and PC users can also use keyboard to select non-adjacent cells. Shift+F8 (I have link in Canvas to demonstrate)Chart WizardWith the cells you have selected highlighted,Select Insert tab, Select Pie icon in the Charts groupSelect any Pie chart formatThe chart will overlap your cells; move the chart to the column G.Excel will build a chart with the cells you have highlighted. Excel has a number of built-in features to help you create charts.Change the chart title to: your name House Hold BudgetBe sure Data Labels and Legend are selectedYour worksheet and chart should look like this, now would be an excellent time to save your workbook as your_name_expenses.xlsx.The formulas in your worksheet and the chart is related. For example, if MS Edwards received a raise and is making 3600 per month in January, February, and March. Changing cells B8, C8, D8 from 2500 to 3600 will cause a chain reaction. The formulas in cells: E8, B10, C10, D10, E10, B23, C23, D23 will recalculate AND the chart will change. Try IT!!!Assignment #2: fundraiser spreadsheetCreate a New Spreadsheet, select File, NewTo review what you have learned, you will create a new spreadsheet, enter the data below, save the spreadsheet as your_name_fundraiser.xlsx Use the clues below to solve the problem. Only cell B6 has a number, use formulas for all other cells. Only cell B6 has a number, use formulas for all other cells.Only cell B6 has a number, use formulas for all other cells.Cookbooks:Joey sold 6 cookbooks more than RachelRachel sold twice as many cookbooks as GinnyRon sold half as many cookbooks as JoeyTom sold 3 cookbooks less than GinnyGinny sold 18 cookbooksGifts:Joey sold 9 gifts less than Ginny sold giftsRachel sold 4 more cookbooks than she sold giftsRon sold 6 times as many gifts as Ginny sold in cookbooksGinny sold half as many gifts as she sold cookbooksTom sold half as many gifts as Joey sold in cookbooks. Then he sold an additional 5 giftsCandy:Joey sold 7 times as many candy orders as Ginny, but 14 of those orders were cancelledRachel sold twice as many candy orders as Joey, and then two of those orders were cancelledRon sold a fourth as many candy orders as Rachel sold in cookbooksGinny sold half as many candy orders as Ron sold in giftsTom sold the same number of candy orders as Joey sold in cookbooks less what Ginny sold in giftsTotal $ Per Person:If each cookbook sale raised $5, each gift sale raised $7, and each candy order sale raised $.50, how much money did each person raise? Put the appropriate formulas in the column E3 thru E7Total $ Per Item:If each cookbook sale raised $5, each gift sale raised $7, and each candy order sale raised $.50, how much money did each item raise? Put the appropriate formulas in the column B8 thru D8Total $ (cell E8):What is the total amount of money raised; Put the appropriate formulas in the cell E8Format the spreadsheet;See tutorial on formatting cells: cell width appropriatelyApply currency formatCell A1: format as Heading 2 cell styleCells A8 thru E8: format as Total cell styleCells B2 thru E2: format as Heading 4 cell styleWhen done, your spreadsheet should look like this;Save the spreadsheet as your_name_fundraiser.xlsxNOW, Ginny has really sold 23 cookbooks, update cell B6. ALL THE NUMBERS IN THE SPREADSHEET SHOULD RECALCULATE ALL THE NUMBERS IN THE SPREADSHEET SHOULD RECALCULATE. ALL THE NUMBERS IN THE SPREADSHEET SHOULD RECALCULATE If your spreadsheet does NOT look like this, review and fix the formulasNOW Ginny has sold only 12 cookbooks, update cell B6. ALL THE NUMBERS IN THE SPREADSHEET SHOULD RECALCULATE ALL THE NUMBERS IN THE SPREADSHEET SHOULD RECALCULATE ALL THE NUMBERS IN THE SPREADSHEET SHOULD RECALCULATENOW Ginny has sold 100 cookbooks (go Ginny), update cell B6. ALL THE NUMBERS IN THE SPREADSHEET SHOULD RECALCULATESorry, Ginny made a mistake, she has sold only 18 cookbooks, update cell B6. ALL THE NUMBERS IN THE SPREADSHEET SHOULD RECALCULATESave the spreadsheet as your_name_fundraiser.xlsxSubmit all files via Canvas ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download