IT SKILLS LAB-2

2021

Submitted For The Partial Fulfillment Of The Requirement For Award Of Degree
OF
MASTER OF BUSINESS ADMINISTRATION
FROM
DR. A. P. J. ABDUL KALAM TECHNICAL UNIVERSITY, LUCKNOW

SUBMITTED BY:

MBA 1ST SEM

SUBMMITED TO

DEPARTMENT OF BUSINESS ADMINISTRATION

RAJSHREE INSTITUTE OF MANAGEMENT AND

TECHNOLOGY, BAREILLY
Table of contents

.) About Pivot Files

.) Scenario Manager if Excel

.) Data validation in excel

.) Edit data validation in Excel

.) Types of charts in Excel

.) Add or remove data levels in charts

About PivotTables
A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer
unanticipated questions about your data. A PivotTable is especially designed for:

▪ Querying large amounts of data in many user-friendly ways.

▪ Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas.
▪ Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you.
▪ Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data.
▪ Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data enabling you to focus on just the information
you want.
▪ Presenting concise, attractive, and annotated online or printed reports.

Create a PivotTable in Excel for Windows

1. Select the cells you want to create a PivotTable from.

Note: Your data shouldn't have any empty rows or columns. It must have only a single-row heading.

2. Select Insert > PivotTable.

3. Under Choose the data that you want to analyze, select Select a table or range.

4. In Table/Range, verify the cell range.

5. Under Choose where you want the PivotTable report to be placed, select New worksheet to place the PivotTable in a new worksheet or Existing
worksheet and then select the location you want the PivotTable to appear.
6. Select OK.

Building out your PivotTable

1. To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.

Note: Selected fields are added to their default areas: non-numeric fields are added to Rows, date and time hierarchies are added to Columns,
and numeric fields are added to Values.

2. To move a field from one area to another, drag the field to the target area.
What is Scenario Manager in Excel?

• Scenario manager in excel is a part of three what-if-analysis tools in excel, which are built-in in, excel. In simple terms, you can see the impact of
changing input values without changing the actual data. Like Data Table in excel, you now input values that must change to achieve a specific goal.
• Scenario Manager in Excel allows you to change or substitute input values for multiple cells (maximum up to 32). Therefore, you can view the results
of different input values or different scenarios at the same time.
• For Example: What if I cut down my monthly traveling expenses? How much will I save? Here scenarios can be stored so that you can apply them
with just a click on the mouse.

How to Use Scenario Manager Analysis Tool in Excel?

Scenario Manager is very simple and easy to use in excel.

Scenario Manager in Excel – Example #1

A simple example could be your monthly family budget. You will spend on food, travel, entertainment, clothes, etc.… and see how these affect your overall
budget.

Step 1: Create a below table shows your list of expenses and income sources.

• In cell B5, you have total income.

• In cell B17, you have total expenses for the month.

• In cell B19, total money left.

You are ending up with only 5,550 after all the expenses. So, you need to cut down your cost to save more for the future…

Step 2: From the top of Excel, click the Data menu > On the Data menu, locate the Data Tools panel > Click on the what-if-Analysis item and select the Scenario
Manager in excel from the menu.

Step 3: When you click on the Scenario Manager below, the dialogue box will open.

Step 4: You need to create a new scenario. So click on the Add button. Then you will get the below dialogue box.

By default, it shows the cell C10, which means that it is the currently active cell. First, type the Scenario Name in the box as the Actual Budget.
Now, you need to enter which cells your excel sheet will be changing. In this first scenario, nothing will be changing because this is my actual budget for the
month. Still, we need to specify the cells will be changing.

Now try to reduce your Food expenses and Clothes expenses. These are in the cells B15 & B13, respectively. Now your add scenario dialogue box should look
like this.

Click, OK, and Excel will ask you for some values. Since we do not want any changes to this scenario, just click OK.

Now, you will be taken back to the Scenario Manager Box. Now the window will look like this.

Now, one scenario is done and dusted. Create a second scenario, and this where you need to make changes to your Food & Clothes expenses.

Click the Add button one more time and give a scenario name as “Plan 2”. Changing the cell will be B15 & B13 (Food & Cloth expenses).
Now, below Scenario Values dialogue box opens again. This time, we do want to change the values. Enter the same ones as in the image below:

These are the new values for our new scenario, Plan 2. Click OK, and now you are back to the Scenario Manager window. Now we already have two scenarios
named after Actual Budget & Plan 2.

Click the Add button one more time and give a scenario name as “Plan 3”. Changing the cell will be B15 & B13 (Food & Cloth expenses).

Now, below Scenario Values dialogue box opens again. This time, we do want to change the values. Enter the same ones as in the image below:
These are the new values for our new scenario, Plan 3. Click OK, and now you are back to the Scenario Manager window. Now you have three scenarios named
after Actual Budget, Plan 2, and Plan 3.

As you can see, we have our Actual Budget, Plan 1 and Plan 2. With Plan 2 selected, click the Show button at the bottom. The values in your excel sheet will
change, and the new budget will be calculated. The image below shows what it looks like.

Click on the Actual Budget, then click on the Show button to see the differences. Initial values will be displayed.

Do the same for Plan 2 to look at the changes.

So Scenario Manager in Excel allows you to set different values and allows you to identify the significant changes from them.

What is data validation in Excel?

Excel Data Validation is a feature that restricts (validates) user input to a worksheet. Technically, you create a validation rule that controls what kind of data can
be entered into a certain cell.

Here are just a few examples of what Excel's data validation can do:

• Allow only numeric or text values in a cell.

• Allow only numbers within a specified range.
• Allow data entries of a specific length.
• Restrict dates and times outside a given range.
• Restrict entries to a selection from a drop-down list.
 • Validate an entry based on another cell.
• Show an input message when the user selects a cell.
• Show a warning message when incorrect data has been entered.
• Find incorrect entries in validated cells.

For instance, you can set up a rule that limits data entry to 4-digit numbers between 1000 and 9999. If the user types something different, Excel will show an error
alert explaining what they have done wrong:

How to do data validation in Excel

To add data validation in Excel, perform the following steps.

1. Open the Data Validation dialog box

Select one or more cells to validate, go to the Data tab > Data Tools group, and click the Data Validation button.

You can also open the Data Validation dialog box by pressing Alt > D > L, with each key pressed separately.

2. Create an Excel validation rule

On the Settings tab, define the validation criteria according to your needs. In the criteria, you can supply any of the following:

• Values - type numbers in the criteria boxes like shown in the screenshot below.
• Cell references - make a rule based on a value or formula in another cell.
• Formulas - allow to express more complex conditions like in this example.

As an example, let's make a rule that restricts users to entering a whole number between 1000 and 9999:

With the validation rule configured, either click OK to close the Data Validation window or switch to another tab to add an input message or/and error alert.

3. Add an input message (optional)

If you want to display a message that explains to the user what data is allowed in a given cell, open the Input Message tab and do the following:
 • Make sure the Show input message when cell is selected box is checked.
• Enter the title and text of your message into the corresponding fields.
• Click OK to close the dialog window.

As soon as the user selects the validated cell, the following message will show up:

4. Display an error alert (optional)

In addition to the input message, you can show one of the following error alerts when invalid data is entered in a cell.

Alert type Description

Stop (default)

The strictest alert type that prevents users from entering invalid data.

You click Retry to type a different value or Cancel to remove the entry.

Warning

Warns users that the data is invalid, but does not prevent entering it.

You click Yes to input the invalid entry, No to edit it, or Cancel to remove the entry.
 Information

The most permissive alert type that only informs users about an invalid data entry.

You click OK to enter the invalid value or Cancel to remove it from the cell.

To configure a custom error message, go to the Error Alert tab and define the following parameters:

• Check the Show error alert after invalid data is entered box (usually selected by default).
• In the Style box, select the desired alert type.
• Enter the title and text of the error message into the corresponding boxes.
• Click OK.

How to edit data validation in Excel

To change an Excel validation rule, perform these steps:

1. Select any of the validated cells.

2. Open the Data Validation dialog box (Data tab > Data Validation).

3. Make the required changes.

4. Select the Apply these changes to all other cells with the same settings check box to copy the changes you've made to all other cells with the
original validation criteria.

5. Click OK to save the changes.

For instance, you can edit your Excel data validation list by adding or removing items from the Source box, and have these changes applied to all other cells
containing the same drop-down list:

What is a chart?
A chart is a visual representative of data in both columns and rows. Charts are usually used to analyse trends and patterns in data sets.
Types of Charts in MS Excel
Different scenarios require different types of charts. Towards this end, Excel provides a number of chart types that you can work with. The type of chart that
you choose depends on the type of data that you want to visualize. To help simplify things for the users, Excel 2013 and above has an option that analyses
your data and makes a recommendation of the chart type that you should use.

Types of Charts in MS Excel

Different scenarios require different types of charts. Towards this end, Excel provides a number of chart types that you can work with. The type of chart that you
choose depends on the type of data that you want to visualize. To help simplify things for the users, Excel 2013 and above has an option that analyses your data
and makes a recommendation of the chart type that you should use.

The following table shows some of the most commonly used Excel charts and when you
should consider using them.

S/N CHART TYPE WHEN SHOULD I USE IT? EXAMPLE

1 Pie Chart When you want to quantify items and show them as percentages.

2 Bar Chart When you want to compare values across a few categories. The values run horizontally

3 Column chart When you want to compare values across a few categories. The values run vertically

4 Line chart When you want to visualize trends over a period of time i.e. months, days, years, etc.

5 Combo Chart When you want to highlight different types of information

Add or remove data labels in a chart

Data labels make a chart easier to understand because they show details about a data series or its individual data points. For example, in the pie chart below,
without the data labels it would be difficult to tell that coffee was 38% of total sales. Depending on what you want to highlight on a chart, you can add labels to
one series, all the series (the whole chart), or one data point.

Add data labels to a chart

1. Click the data series or chart. To label one data point, after clicking the series, click that data point.

2. In the upper right corner, next to the chart, click Add Chart Element > Data Labels.

3. To change the location, click the arrow, and choose an option.

4. If you want to show your data label inside a text bubble shape, click Data Callout.

To make data labels easier to read, you can move them inside the data points or even outside of the chart. To move a data label, drag it to the location you want.

If you decide the labels make your chart look too cluttered, you can remove any or all of them by clicking the data labels and then pressing Delete.