Lab 5 - Ms Excel Tutorial

MS Excel 2007 Tutorial Worksheet Basics Excel is the world’s most widely-used spreadsheet program, and is part of the Microsoft Office suite. Excel’s forte, of course, is performing numerical calculations, but Excel is also very useful for non-numerical applications. Here are just a few of the uses for Excel: •

Number crunching: Create budgets, analyze survey results, and perform just about any type of financial analysis you can think of.

•

Creating charts: Create a wide variety of highly customizable charts.

•

Organizing lists: Use the row-and-column layout to store lists efficiently.

•

Accessing other data: Import data from a wide variety of sources.

•

Creating graphics and diagrams: Use Shapes and the new SmartArt to create professional-looking diagrams.

•

Automating complex tasks: Perform a tedious task with a single mouse click with Excel’s macro capabilities.

Excel 2007 Ribbons

Excel 2007 has eight standard ribbon tabs (nine if you count Developer – shown in the image above; has to be turned on by the user) and an occasional “contextual” tab that shows up when you have a certain item selected. For example, if you have a picture selected, a Picture Tools: Format ribbon is available, as show in the figure below.

Home Ribbon

The Home ribbon has common formatting and edit commands. Insert Ribbon

The Insert ribbon allows you to insert common objects, charts, links, images, and more. Page Layout Ribbon

The Page Layout ribbon is used to layout your spreadsheets for printing. Formulas Ribbon

The Formulas ribbon allows access to different formulas so you don’t have to memorize all of the functions. Data Ribbon

The Data ribbon has commands for accessing external data, sorting and filtering, and managing data in the spreadsheet.

Review Ribbon

The Review ribbon contains the proofing and reviewing tools. If you have a tablet PC, you can also mark up the spreadsheet with “Ink.” View Ribbon

The View ribbon has different views available, allows you to control the zoom view of the document, and lets you access Macros.

Create a Worksheet Select Office button > New. This step pops up the New Workbook window. The New Workbook window lets you create a new, blank workbook or a ready-made workbook from a template.

How to use Formulas and Functions in Excel SUM function Click in cell any cell and type in “=sum(” without the quotes. The = sign tells Excel that we plan on putting some type of formula into this cell. When you type in the first parenthesis, Excel will display a little label showing you what types of variables this function takes.

At this point after the first parenthesis, you can select the range of cells you want to sum up.! No need to type the cells one by one! Go ahead and select cells B2 to D2 and you will see that the formula is automatically updated and is in blue.

After you select the range, type in the closing parenthesis (Shift + 0) and press Enter. And now you have the sum of the numbers. There’s an easy way to copy your formula automatically for the other students. Click on cell F2 and then move your mouse slowly to the lower right edge of the cell. You’ll notice that the cursor changes from a fat white cross to a skinny black cross and the bottom right of the cell is a small black box.

Click on that small black box and hold your mouse down and drag it to the row of the last student. And with that, Excel uses the same formula, but updates the current row cells so that the sum is calculated for each row using that row’s data.

You can also click on the fx on the formula bar and it will show variety of functions that are available.

Charts and Graphs Excel is a powerful tool that you can use to create charts and graphs for small or large amounts of data. In this Excel tutorial, I’ll show you how to take a small set of data and create a simple bar graph, along with the options you have to customize the graph. Once you have the basics down, you can use those same techniques on larger sets of data. First off, I’ve created a set of student test data for our example. There are eight students with their test scores on four exams. To make this into a chart, you first want to select the entire range of data, including the titles (Test 1, etc).

Now that your data is selected as shown above, go ahead and click on the Chart Wizard button in the Excel toolbar. The button has a little graph inside of it as the icon. If you can’t see the icon or it’s not on your toolbar, you can click on the Insert menu and choose Chart.

15th November, 2007

LAB Task: 1. Make a worksheet with subject names and their corresponding marks in at least 3 quizzes. 2. In one column take the sum of all the quizzes for each student. 3. In the next column take the average of the marks of each quiz. 4. In the next column determine whether each student is pass or fail using IF function. Student has passed if quiz average greater than 6.5. 5. Sort the whole data using sorting in the Home->Editing->Sort & Filter in the ascending order (i.e. A to Z). 6. Draw a column and pie graph for the data above. 7. Explore POWER, SQRT, MAX, MIN functions.