PRACTICAL FILE FOR IT SKILLS LAB 2
SESSION - 2022-2023
SUBJECT CODE – KMBN251
SUBMITTED TO : SUBMITTED BY:
Shruti Varshney
Ms.Anjali Kuswaha 2212000700178
1
S. No Name of Practical’s Page No Teacher
Signature/
Remarks
1 Create a column chart in ms excel with data given 3
below
2 Create a bar chart in ms excel 4
3 Create a line chart in ms excel 6
4 Create pie chart in ms excel 8
5 Create XY scatter chart in ms excel 10
6 Create area chart in ms excel 12
7 Create surface chart in ms excel 14
8 Create bubble chart in ms excel 16
9 Create a pivot table in ms excel with dataset given 19
below
10 Steps to use goal seek analysis in ms excel 21
11 Steps to use solver in ms excel 23
12 Create a scenarios in ms excel 28
13 Apply data validation for whole number in ms excel 35
14 Apply data validation for date number in ms excel 37
15 Apply data validation for text length in ms excel 39
16 Apply data validation for decimal in ms excel 40
17 Apply data validation for drop down list from fetching 41
data from worksheet in ms excel
18 Apply data validation for drop down listing ms excel 42
19 Show precedent tracer arrows in ms excel 43
20 Showtrace dependents arrows in ms excel 45
21 To create and run Macro in ms excel 46
2
PRACTICAL 01
OBJECTIVE: CREATE A COLUMN CHART IN MS EXCEL WITH
DATA GIVEN BELOW
STEP 1:Click Insert > Insert Column or Bar Chart > Clustered
Column
In just a few clicks, we have made the column chart below.
3
PRACTICAL 02
OBJECTIVE: CREATE A BAR CHART IN MS EXCEL
STEP 1: Select the range A1:B6.
STEP 2: On the Insert tab, in the Charts group, click the Column symbol.
STEP 3: Click Clustered Bar.
4
Result:
5
PRACTICAL 03
OBJECTIVE: CREATE A LINE CHART IN MS EXCEL
STEP 1:Select the range A1:D7.
STEP 2:On the Insert tab, in the Charts group, click the
Line symbol.
STEP 3:Click Line with Markers.
6
Result:
7
PRACTICAL 04
OBJECTIVE: CREATE PIE CHART IN MS EXCEL
STEP 1:Select the range A1:D7.
STEP 2: On the Insert tab, in the Charts group, click the Pie
symbol.
STEP 3: Click Pie
Result:
8
9
PRACTICAL 05
OBJECTIVE: CREATE XY SCATTER CHART IN MS EXCEL
STEP 1:Select the range A1:B10.
STEP 2: On the Insert tab, in the Charts group, click the
Scatter symbol.
STEP 3:Click Scatter.
Result:
10
11
PRACTICAL 06
OBJECTIVE: CREATE AREA CHART IN MS EXCEL
STEP 1: Select the range A1:D7.
STEP 2: On the Insert tab, in the Charts group, click
the Line symbol.
STEP 3: Click Area.
12
Result.
13
PRACTICAL 07
OBJECTIVE: CREATE SURFACE CHART IN MS EXCEL
STEP 1: Select the range A1:F5
STEP 2: Under the “INSERT” tab, we can see the “Chart”
section. Choose “Stock,” “Surface,” or Radar Chart in
excel in this section. Under this category of charts,
choose the “3-D Surface” chart.
STEP 3: Click on the “3-D Surface” chart, you will
see the below default surface chart in your
worksheet.
14
15
PRACTICAL 08
OBJECTIVE: CREATE BUBBLE CHART IN MS EXCEL
STEP 1: Select the range A1:D5
STEP 2:Goes to “Insert” and “Recommended Charts”
and select the bubble chart, as shown below.
STEP 3: Next, we must create an Excel Bubble Chart with
the below formatting.
Format X-axis
16
STEP 4: Format Y-axis and colour the bubble.
STEP 5: Now, we need to add data labels manually.
Right- click on bubbles and select add data labels.
Select one by one data label and enter the region
names manually.
17
So finally, our chart should look like the one below.
18
PRACTICAL 9
OBJECTIVE: CREATE A PIVOT TABLE IN MS EXCEL WITH DATASET
GIVEN BELOW
STEP 1:Click anywhere in the dataset.
STEP 2:Go to Insert –> Tables –> Pivot Table
.
STEP 3:In the Create Pivot Table dialog box, the
default options work fine in most of the cases. Here
are a couple of things to check in it:
Table/Range: It’s filled in by default based
on your data set. If your data has no blank
rows/columns, Excel will automatically
identify the correct range. You can manually
change this if needed.
If you want to create the Pivot Table in a
specific location, under the option ‘Choose
where you want the PivotTable report to be
19
placed’, specify the
11
0
Location. Else, a new worksheet is created with
the Pivot Table.
STEP 4: Click OK.
As soon as you click OK, a new worksheet is
created with the Pivot Table in it.
While the Pivot Table has been created, you’d see no
data in it. All you’d see is the Pivot Table name and a
single line instruction on the left, and Pivot Table
Fields on the right.
20
PRACTICAL 10
OBJECTIVE: STEPS TO USE GOAL SEEK ANALYSIS IN MS EXCEL
STEP 1: Select the cell with the output you want to change.
STEP 2:Navigate to the Datatab& select “What -if Analysis”
STEP 3: Click “Goal Seek”
21
STEP 4: Type the number you want to hit into the “To
value”
field.
STEP 5: Select the variable you want to change in the
“By changing cell” box.
STEP 5: Click “OK” to see the Goal Seek analysis.
22
PRACTICAL 11
OBJECTIVE: STEPS TO USE SOLVER IN MS EXCEL
STEP 1: Go to File and select options. The following dialog
box will appear.
STEP 2: Now select the Add-ins option and click on Go
and finally click on OK. Select Solver Add-in and press
OK
23
Now solver will appear in data section like
this. Now let’s understand how to use solver with the
help of an example.
We went to a mall, and we also have a gift voucher worth
rs.10,000 and We want to purchase items in such a
manner that all the money of the gift voucher gets
utilized.
Suppose we purchased the above items in only one
quantity and the total came out to be 9300 but the
voucher was for rs.10,000. So now we want to use
Solver for this purpose.
Now let’s see how it will be done.
STEP 3: Firstly, go on data and find solver there and click
24
it. The following dialog box will appear. Now in this, we
have to select the objective in which we want to
change our value.
25
STEP 4: In the set objective we must select the total of
the D column because want the value to change from
9300 to 10,000. After clicking on D7 following thing
will be displayed on the set aim block.
STEP 5: Now in the ‘By changing Variable cell’ we will
select the Quantity cell because we want to change the
quantity in such a way so that the total amount comes
to 10,000.
26
STEP 6: Now we have to set some conditions under
which we want our work to get done. So, for setting
some conditions
/constraints, we will click on Add.
STEP 7: Now, a dialog box will appear, and we will add 3
conditions. The first condition is that the total amount should be
equal to the voucher amount. So, we will select cell D7 in a cell
reference and then
= sign, and finally, we will select cell C11. Now the first
condition is added. To add the next condition press, Add.
27
STEP 8: For the second condition we will select the Quantity
cell because we want the quantity to be an integer value, a
whole value. So, in cell reference, we will select from B2 to B6
then int, and then again will press Add.Now for the third
condition, we want that our item quantity should never be un
negative which is not possible in real life. So, we will select
cells from B2 to B6 and should be >= to zero. Then click Add
and cancel.
STEP 9: Now the following dialog box will show all the 3
conditions that we used and now click on Solve.
28
STEP 10: By clicking Solve the solver will provide the
desired output and to keep the answer we will click on
keep solver solution.
So, this is what a solver is, how it is activated by not by default present in
Excel and this is how it is used.
PRACTICAL 12
OBJECTIVE: CREATE A SCENARIOS IN MS EXCEL
Step 1: Create a below table that shows your list of
expenses and income sources.
29
You are ending up with only 5,550 after all the expenses.
So, it would help if you cut 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 dialog box will open.
21
0
Step 4: You need to create a new scenario. So, click on
the Add button. Then, you will get the below dialog box.
By default, it shows cell C10, which means it is the
currently active cell. So, 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. Nothing will change in this first scenario because
this is my actual budget for the month. Still, we need to
specify the cells that will be changing.
30
Step 5 : Now, try to reduce the food expenses and
clothes expenses. These are in cells B15 and B13,
respectively. Now, the add scenario dialog box should
look like this.
Step 6: Click “OK.” Excel will ask you for some values.
Since we do not want any changes to this scenario, click
“OK.”
31
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. Next, create a
second scenario where you must change your food and
clothes expenses.
Step 7 : Click the Add button and give a “Scenario Name”
as “Plan 2”. “Changing the cell” will be B15 and B13
(food and cloth expenses).
Now, below, the “Scenario Values” dialog box opens again.
This time, we want to change the values. Enter the same
ones as in the image below:
32
These are the new values for our new scenario, Plan 2.
Click “OK.” Now, you are back to the Scenario Manager
window. Now, we have two scenarios named after Actual
Budget and Plan 2.
Step 8 : Click the Add button and give a scenario name as
“Plan
3.” “Changing cells” will be B15 and B13 (food and cloth
expenses).
33
Step 9: Now, below, the “Scenario Values” dialog box
opens again. This time, we do want to change the values.
Insert the same ones as in the image below:
These are the new values for our new scenario, Plan 3.
Click “OK.” 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 2,”
and
“Plan 3.” With “Plan 2” selected, click the “Show” button
at the bottom. The values in your Excel sheet will change,
and we will calculate the new budget. The image below
shows what it looks like.
34
Click on the Actual Budget and the Show button to see the
differences. It will display initial values.
Do the same for “Plan 2” to look at the changes.
35
PRACTICAL 13
OBJECTIVE: APPLY DATA VALIDATION FOR WHOLE NUMBER IN MS
EXCEL
Step 1: Go to Data tab>>Data Validation>>Click on Data Validation.
Step 2: Select Whole number in Allow drop down.
Step 3: Select between in Data drop downput Minimum and Maximum value.
Whole Number in Data Validation Window
As we have given the minimum 10 and maximum 20 in the criteria. So
whole numbers between 10 to 20 will be allowed to enter. if any other
whole number or text will be entered, it will show “The value you
entered is not valid”.
36
Whole Number Data Validation
while entering the decimal number it will show an error.
37
PRACTICAL 14
OBJECTIVE: APPLY DATA VALIDATION FOR DATE NUMBER IN MS
EXCEL
Step 1: First, select the cell where you want to apply this data
validation rule.
Step 2: Go to Data Tab ⇢ Data Validation ⇢ Data Validation.
Step 3: From here in the data validation dialog box, select
“Date” from the “Allow” drop-down.
Step 4: After that, select between from the “Data” drop-down.
Step 5: Next, you need to enter two dates in the “Start Date”
and “End Date” input boxes.
38
Now, in all those cells which you have selected user can
only enter a date that is within the range of those dates
which you have specified.
Even, instead of entering dates directly into the data
validation, you can refer to the cells where you have dates.
This way you can change dates at any time without
opening the option. You can use the TODAY function to
create a dynamic range of dates.
39
PRACTICAL 15
OBJECTIVE: APPLY DATA VALIDATION FOR TEXT LENGTH IN MS EXCEL
Step 1: Select the cells you want to limit digits, click Data > Data
Validations > Data Validation.
Step 2: Under the Settings tab, select Text length from Allow drop-
down list, choose between in Data drop-down list
in Minimum and Maximum textbox, type the number you use.
Step 3 : Click OK. Now the selected cells only can type strings in
digits between 7 and 10.
40
PRACTICAL 16
OBJECTIVE: APPLY DATA VALIDATION FOR DECIMAL IN MS EXCEL
Step 1: Select the cells you want to limit digits, click Data > Data
Validations > Data Validation.
Step 2: Under the Settings tab, select DECIMAL from Allow drop-
down list, choose between in Data drop-down list
in Minimum and Maximum textbox, type the number you use.
Step 3: Click OK. Now the selected cells only can type strings in
digits between and2.2
41
PRACTICAL 17
OBJECTIVE: APPLY DATA VALIDATION FOR DROP DOWN LIST FROM
FETCHING DATA FROM WORKSHEET IN MS EXCEL
Step 1: Select the cells you want to limit digits, click Data > Data
Validations > Data Validation.
Step 2:Step 2: Under the Settings tab, select LIST rom Allow drop-
down list, Left click once in the ‘Source:’ field. And select the cells containing
your drop-down list items.
Step 2:After you click ‘OK’ your drop-down list is ready for use.
42
PRACTICAL 18
OBJECTIVE: APPLY DATA VALIDATION FOR DROP DOWN LISTIN MS
EXCEL
Step 1: Select the cells you want to limit digits, click Data > Data
Validations > Data Validation.
Step 2: Under the Settings tab, select LIST rom Allow drop-down list, Left
click once in the ‘Source:’ field and fill that field
Step 3:Click Ok you get you list shown below.
43
PRACTICAL 19
OBJECTIVE: SHOW PRECEDENT TRACER ARROWS IN MS EXCEL
Step 1: The first step is to select a cell for which you
want to show precedent tracer arrows.
Step 2: Then in the Ribbon, go to Formulas >
Trace Dependents (from the Formula Auditing
group).
Step 3: Precedent tracer arrows will appear for the selected
cell. You can see below.
44
Step 4: If there is more than one layer, you can click on
Trace Precedents again and it will show other layers with
new tracer arrows.
45
PRACTICAL 20
OBJECTIVE: SHOWTRACE DEPENDENTS ARROWSIN MS EXCEL
Step 1: Select cell you need Trace Dependents.
Step 2: Go toFormulas > Trace Dependents.
RESULT:
46
PRACTICAL 21
OBJECTIVE: To create and run Macro in MS EXCEL
Step 1: Open MS Word and Click on View Tab.
Step 2: Select Macro and click on Record Macro option.
Click here.
Click Here
Step 3: Start performing the task you want to record in your Macro,
here we are creating thelayout of a letter.
47
Click here to add your desire shortcut.
Step 3: Select Stop Recording.
Step 4: Now to use Macro Click Macro and Select View Macro on a
new file.
Step 5: Click Run Macro to automatically performs the above task.
48
