Skip to document
Welcome to StudocuSign in to access the best study resources
Guest user
0followers
HomeMy LibraryAsk AI
  • You don't have any recent items yet.
My Library
  • You don't have any courses yet.
  • You don't have any books yet.
  • You don't have any Studylists yet.

Scribd

Microsoft Excel
Course

Data Analytics - Trainity

28 Documents
Students shared 28 documents in this course
Academic year: 2023/2024
Uploaded by:
Student
Dr. Anand R
Visvesvaraya Technological University
0followers
1Uploads
0upvotes

Recommended for you

Comments

Please sign in or register to post comments.

Students also viewed

Related documents

Preview text

1

Peeking into Computer Science |

Excel Lab Manual

Lab Manual

Spreadsheets

Microsoft Excel

Peeking into Computer Science | Excel Lab Manual -

  • Lab 1: Excel Basics Table of Contents
    • The interface
    • Auto-complete
    • Formatting
    • Basic calculations
  • Lab 2: Charts and Pivot Tables
    • Charts
    • Pivot tables..............................................................................................................................................
  • Lab 3: Conditionals and Lookup Tables
    • Conditional functions
    • IF functions..............................................................................................................................................
    • Lookup functions.....................................................................................................................................
  • Lab 4: Conditional Formatting and Lists
    • Conditional formatting............................................................................................................................
    • Sorting lists
    • Filtering lists
  • Lab 5: Drop-Down Lists and Dynamic Charts
    • Drop-down lists
    • OFFSET function
    • Dynamic charts........................................................................................................................................
  • Lab 6: Interactive Content
    • Using combo boxes to create dynamic charts
    • Using check boxes to create dynamic charts
    • What’s next?

Lab 1: Excel Basics Table of Contents

4 Peeking into Computer Science | Excel Lab Manual

7. The worksheet toolbar: An Excel file is called a workbook. It consists of a number of spreadsheets

(worksheets). This toolbar allows you to move between the different sheets in a workbook. It also

allows you to create new worksheets, delete existing sheets, and rename sheets.

Exercise 1

1. Enter the data “Sunday” into cell A1 and “Monday” into cell B1.

2. Type in “17/08” into cell E8.

3. Type in “2” into cell I8 and “4” into cell I9.

Auto-complete

Your worksheet should now look like this:

Notice how Excel automatically detected that 17/08 was a date and converted it to 17-Aug. We will

discuss formatting data later on in this lab.

Now, we want to select both cells A1 and B1 together. To do this, click A1 and without releasing the

mouse button, move the mouse over cell B1. Now there should be a rectangle around both cells as

shown below.

Lab 1: Excel Basics

5 Peeking into Computer Science | Excel Lab Manual

To get excel to auto-complete this row, we now position the mouse cursor at the bottom-left corner of

the rectangle. Make sure the cursor has changed into a + sign. Now hold down the cursor and drag it to

the left till I1.

This is what your spreadsheet should look like when you release the mouse button:

Lab 1: Excel Basics

7 Peeking into Computer Science | Excel Lab Manual

Select currency from the drop down menu. Now, you will notice that the two numbers have a $ sign

preceding them, and have two decimal places. Let us change the currency symbol to a Euro. Select the

Euro symbol from the currency format menu.

Exercise 3

Modify cells I8 and I9 by removing the 2 decimal places.

Exercise 4

Format cell E8 so that it looks like August 17, 2010.

Basic calculations

When working on a spreadsheet, you will almost definitely need to perform some calculations on the

data you have. The first thing you need to remember about Excel calculations is that formulas always

start with an = sign. Let us begin with a very simple calculation. Type “=3+5” into cell A5 as shown

below.

Lab 1: Excel Basics

8 Peeking into Computer Science | Excel Lab Manual

Press Enter. Excel automatically replaces the formula with the result of the equation.

Now let us calculate the sum of the numbers in I8 and I9. In cell J10, type “=I8+I9”. One other option is

to type in “=”, then select cell I8. After that, type in “+” and then select I9.

Lab 2: Charts and Pivot Tables

10 Peeking into Computer Science | Excel Lab Manual Lab 2: Charts and Pivot Tables

Charts

Turning data into charts can help visualize the information at hand. To convey the appropriate message

from your data, you need to select an appropriate type of chart for your data.

Let us start out with creating a simple chart using the chart wizard. Open the file excellab2. In the

total expenses sheet, you will see a table showing the total expenses accumulated by a company in the

12 months of a particular year. Select the entire table.

On the ribbon, click the Insert tab. There you will notice the Charts group, showing all the different types

of charts that can be created out of the data. Select the 1st option in 2D bars (the clustered bar chart).

Lab 2: Charts and Pivot Tables

11 Peeking into Computer Science | Excel Lab Manual

This is the chart Excel creates:

You may need to drag the corner of the chart to make it larger so that all the months are clearly visible

on the y-axis. The chart needs some tweaking. First, we need to decide what extra information we want

the chart to show. We want this chart to have a meaningful title and show a title for the x-axis. We do

Lab 2: Charts and Pivot Tables

13 Peeking into Computer Science | Excel Lab Manual

The chart now shows an x-axis title:

Exercise 1

Remove the legend through the Labels group in the Layout tab. If needed, adjust the chart size so that

all the data are clearly visible.

Lab 3: Conditionals and Lookup Tables

14 Peeking into Computer Science | Excel Lab Manual

Now that the layout of the chart is what we want, let us enter the correct titles. Click the chart title and

replace it with “Total expenses for 2009”. Do the same for the axis title and replace it with “Expenses

($)”.

Now we can play around with the chart colors. Again, Excel allows you to either select one of its

predefined themes or to change the background and colors, one at a time. Select Style 25 from the

Chart Styles group in the Design tab.

Lab 2: Charts and Pivot Tables

16 Peeking into Computer Science | Excel Lab Manual

It is quite easy to change the chart type in Excel. To do this, select the chart area and go to the Insert

tab. Select the Clustered Column from the Column charts menu.

This changes the chart type as follows:

Lab 2: Charts and Pivot Tables

17 Peeking into Computer Science | Excel Lab Manual

Exercise 2

Using the same table, create a 2D pie chart. Instead of showing absolute values, we would like the chart

to show the percentages that each segment makes up as a data label. The month name should also be

part of the label and the legend is not needed. The labels should be at the inside end of the data points.

Make sure the chart has a meaningful title. Adjust the chart size so that all the data is clearly visible.

Give the chart area a black border.

Pivot tables..............................................................................................................................................

Pivot tables are used to easily create meaning out of large amounts of data. Let us take an example.

Open the departmental expenses sheet. Select the entire table and then go to the Insert tab on the

ribbon. Click the PivotTable button.

Lab 2: Charts and Pivot Tables

19 Peeking into Computer Science | Excel Lab Manual

This is what you should get:

Let us first take a look at what the different field areas are used for. Their usage will become clear

shortly.

 Values: this is used to display summary calculations in the pivot table.

 Row labels: these labels are used to display fields as rows on the side of the pivot table. The

table is broken down by the first row label; each subsequent label appears as subcategory of the

previous label.

 Column labels: these labels are used to display fields as columns on the top of the pivot table.

Column labels are nested in a similar way to row labels.

 Report filter: this is used to filter the whole report (the pivot table).

The pivot table menu to the right allows you to select the fields you want to add to the table. Select all

of the fields by clicking their respective checkboxes.

Lab 2: Charts and Pivot Tables

20 Peeking into Computer Science | Excel Lab Manual

At this stage, the pivot table does not look like anything that makes sense. We need to tweak it a little

bit to show the information we need. Notice that all the fields have come under Row Labels, except for

Expense, which is under Values.

Drag the Expense Type field to Report Filter. Drag the Department field to the Column Labels.

Was this document helpful?

Scribd

Course: Data Analytics - Trainity

28 Documents
Students shared 28 documents in this course

University: Visvesvaraya Technological University

Was this document helpful?
1
Peeking into Computer Science | Excel Lab Manual
Lab Manual
Spreadsheets
Microsoft Excel

Students also viewed

Related documents