AS PARTIAL FULFILLMENT FOR THE AWARD OF DEGREE IN

MASTERS OF BUSINESS ADMINISTRATIONS

FROM

DR. APJ ABDUL KALAM TECHNICAL UNIVERSITY,

LUCKNOW, UTTAR PRADESH
A, REPORT ON

BMB 251
IT SKILLS LAB – 2
MBA 1st YEAR 2st SEMESTER
SUBMITTED TO
DEPARTMENT OF MANAGEMENT STUDIES

BANSAL INSTITUTE OF ENGINEERING & TECHNOLOGY,

LUCKNOW, UTTAR PRADESH

SUBMITTED BY
AJAY VERMA

BMB 251/ 2024-25

messages.downloaded_
 Acknowledgement

I wish to extend my deepest gratitude to Mr. Puneet Tikkha, of master of business administration
department, and of AKTU UNIVERSITY for their contributions to the completion of my project titled IT
SKILL LAB.

Special thanks to our mentor Mr. Puneet Tikkha for his invaluable advice and suggestions throughout
the year. I am eternally grateful for your time and efforts.

I confirm that this project was completed solely by me and is not the work of someone else.

Name: Ajay Verma

Roll No: 240422070000

BMB 251/ 2024-25

messages.downloaded_
 Table of Contents
Acknowledgement.......................................................................................................................................2
PIVOT TABLE................................................................................................................................................5
Developing Pivot Table............................................................................................................................5
1. Create a PivotTable in Excel........................................................................................................5
2. PivotTables from other sources..................................................................................................6
3. Building out your PivotTable.......................................................................................................7
ANALYZING DATA USING GOAL SEEK AND SOLVER....................................................................................8
GOAL SEEK...............................................................................................................................................8
Step-by-step with an example.................................................................................................................8
Prepare the worksheet........................................................................................................................8
Use Goal Seek to determine the interest rate....................................................................................9
SOLVER...................................................................................................................................................10
Example of solver evaluation.................................................................................................................10
Define and solve a problem...................................................................................................................11
Step through Solver trial solution..........................................................................................................12
Change how Solver finds solutions........................................................................................................13
Save or load a problem model..............................................................................................................13
Solving methods used by Solver............................................................................................................13
SCENARIOS.................................................................................................................................................14
Creating Scenarios.................................................................................................................................14
Creating A Scenario Summary Report...................................................................................................17
VALIDATING AND AUDITING......................................................................................................................20
Apply Data Validation To Cells...............................................................................................................20
Auditing Using Trace Precedents and Dependents...............................................................................20
Trace cells that provide data to a formula (Precedents)...................................................................21
Trace formulas that reference a particular cell (Dependents)..........................................................22
See all the relationships on a worksheet...........................................................................................22
CREATING APPLICATIONS IN SPREADSHEET AND MACROS......................................................................23
CREATING AND FORMATTING CHARTS......................................................................................................27
Create a chart....................................................................................................................................27
Add a trendline..................................................................................................................................27
Understanding Chart Types...................................................................................................................27

BMB 251/ 2024-25

messages.downloaded_
 1. Column Chart.................................................................................................................................27
2. Bar chart........................................................................................................................................28
3. Line chart.......................................................................................................................................28
4. Pie chart.........................................................................................................................................29
5. XY scatter chart..............................................................................................................................29
6. Area chart......................................................................................................................................30
7. Surface chart..................................................................................................................................31
8. Bubble chart..................................................................................................................................32
Combined Chart.....................................................................................................................................32
Column and line chart...........................................................................................................................32
Column and area chart..........................................................................................................................33
Chart Customization in Excel.................................................................................................................33
Change scale of value axis.........................................................................................................................35
1. Change the scale of the vertical (value) axis in a chart.................................................................35
2. Change the scale of the horizontal (category) axis in a chart.......................................................37
Format elements of a chart.......................................................................................................................39
Format your chart using the Format task pane.....................................................................................39
Format your chart using the Ribbon.....................................................................................................40
Use a picture in a chart..........................................................................................................................41
DATABASE MANAGEMENT SYSTEM...........................................................................................................43
1. Creation of Table...............................................................................................................................43
2. Creation of Views...............................................................................................................................43
3. Generating Reports............................................................................................................................44
4. Basics of SQL......................................................................................................................................44
Key SQL Commands:..............................................................................................................................45
1. Data Definition Language (DDL)....................................................................................................45
2. Data Manipulation Language (DML).............................................................................................46
3. Data Query Language (DQL)..........................................................................................................47
4. Data Control Language (DCL)........................................................................................................47
5. TCL - Transaction Control Language..............................................................................................48
Running Queries....................................................................................................................................48

BMB 251/ 2024-25

messages.downloaded_
PIVOT TABLE
A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see
comparisons, patterns, and trends in your data. PivotTables work a little bit differently depending on
what platform you are using to run Excel.

Developing Pivot Table

1. Create a PivotTable in Excel
I. Select the cells you want to create a PivotTable from.
II. Select Insert > PivotTable.

III. This creates a PivotTable based on an existing table or range.

IV. 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 select where you want
the new PivotTable to appear.

V. Select OK.

BMB 251/ 2024-25

messages.downloaded_
 2. PivotTables from other sources
By clicking the down arrow on the button, you can select from other possible sources for your
PivotTable. In addition to using an existing table or range, there are three other sources you can
select from to populate your PivotTable.

Get from External Data Source

Get from Data Model

Use this option if your workbook contains a Data Model, and you want to create a PivotTable
from multiple tables, enhance the PivotTable with custom measures, or are working with very
large datasets.

BMB 251/ 2024-25

messages.downloaded_
 Get from Power BI

Use this option if your organization uses Power BI and you want to discover and connect to
endorsed cloud datasets you have access to.

3. Building out your PivotTable

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

BMB 251/ 2024-25

messages.downloaded_
ANALYZING DATA USING GOAL SEEK AND SOLVER

GOAL SEEK
If you know the result that you want from a formula, but are not sure what input value the formula
needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow
some money. You know how much money you want, how long you want to take to pay off the loan,
and how much you can afford to pay each month. You can use Goal Seek to determine what interest
rate you will need to secure in order to meet your loan goal.

Step-by-step with an example

Let's look at the preceding example, step-by-step.

Because you want to calculate the loan interest rate needed to meet your goal, you use the
PMT function. The PMT function calculates a monthly payment amount. In this example, the
monthly payment amount is the goal that you seek.

Prepare the worksheet

1. Open a new, blank worksheet.
2. First, add some labels in the first column to make it easier to read the worksheet.
a) In cell A1, type Loan Amount.
b) In cell A2, type Term in Months.
c) In cell A3, type Interest Rate.
d) In cell A4, type Payment.
BMB 251/ 2024-25

messages.downloaded_
 3. Next, add the values that you know.
a) In cell B1, type 100000. This is the amount that you want to borrow.
b) In cell B2, type 180. This is the number of months that you want to pay off the loan.
4. Next, add the formula for which you have a goal. For the example, use the PMT function:
a) In cell B4, type =PMT(B3/12,B2,B1). This formula calculates the payment amount. In this
example, you want to pay $900 each month. You don't enter that amount here, because
you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you
start with a formula.

The formula refers to cells B1 and B2, which contain values that you specified in preceding
steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put
the interest rate. The formula divides the value in B3 by 12 because you specified a monthly
payment, and the PMT function assumes an annual interest rate.

Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values
in the example, returns a payment of $555.56. You can ignore that value for now.

Use Goal Seek to determine the interest rate

1. On the Data tab, in the Forecast group, click What-If Analysis, and then click Goal Seek.
2. In the Set cell box, enter the reference for the cell that contains the formula that you want
to resolve. In the example, this reference is cell B4.
3. In the To value box, type the formula result that you want. In the example, this is -900.
Note that this number is negative because it represents a payment.
4. In the By changing cell box, enter the reference for the cell that contains the value that
you want to adjust. In the example, this reference is cell B3.
5. Click OK.
Goal Seek runs and produces a result, as shown in the following illustration.

Cells B1, B2, and B3 are the values for the loan amount, term length, and interest rate.
Cell B4 displays the result of the formula =PMT(B3/12,B2,B1).

6. Finally, format the target cell (B3) so that it displays the result as a percentage.
a) On the Home tab, in the Number group, click Percentage.
b) Click Increase Decimal or Decrease Decimal to set the number of decimal places.

BMB 251/ 2024-25

messages.downloaded_
SOLVER
Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an
optimal (maximum or minimum) value for a formula in one cell—called the objective cell—subject to
constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of
cells, called decision variables or simply variable cells that are used in computing the formulas in the
objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits
on constraint cells and produce the result you want for the objective cell.

Put simply, you can use Solver to determine the maximum or minimum value of one cell by changing
other cells. For example, you can change the amount of your projected advertising budget and see the
effect on your projected profit amount.

Example of solver evaluation

In the following example, the level of advertising in each quarter affects the number of units sold,
indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can
change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget
constraint of $20,000 (cell F5), until the total profit (objective cell F7) reaches the maximum possible
amount. The values in the variable cells are used to calculate the profit for each quarter, so they are
related to the formula objective cell F7, =SUM (Q1 Profit:Q2 Profit).

1. Variable cells

2. Constrained cell

3. Objective cell

After Solver runs, the new values are as follows.

BMB 251/ 2024-25

messages.downloaded_
Define and solve a problem
1. On the Data tab, in the Analysis group, click Solver.

2. In the Set Objective box, enter a cell reference or name for the objective cell. The
objective cell must contain a formula.

3. Do one of the following:

a. If you want the value of the objective cell to be as large as possible, click Max.

b. If you want the value of the objective cell to be as small as possible, click Min.

c. If you want the objective cell to be a certain value, click Value of, and then
type the value in the box.

d. In the By Changing Variable Cells box, enter a name or reference for each
decision variable cell range. Separate the non-adjacent references with
commas. The variable cells must be related directly or indirectly to the objective
cell. You can specify up to 200 variable cells.

4. In the Subject to the Constraints box, enter any constraints that you want to
apply by doing the following:

a. In the Solver Parameters dialog box, click Add.

b. In the Cell Reference box, enter the cell reference or name of the cell range
for which you want to constrain the value.

BMB 251/ 2024-25

messages.downloaded_
 c. Click the relationship ( <=, =, >=, int, bin, or dif ) that you want between the
referenced cell and the [Link] you click int, integer appears in the
Constraint box. If you
click bin, binary appears in the Constraint box. If you click dif,
alldifferent appears in the Constraint box.

d. If you choose <=, =, or >= for the relationship in the Constraint box, type a
number, a cell reference or name, or a formula.

e. Do one of the following:

i. To accept the constraint and add another, click Add.

ii. To accept the constraint and return to the Solver Parameters dialog
box, click OK. Note You can apply the int, bin, and dif
relationships only in constraints on decision variable cells.

You can change or delete an existing constraint by doing the

following:

f. In the Solver Parameters dialog box, click the constraint that you want to
change or delete.

g. Click Change and then make your changes, or click Delete.

5. Click Solve and do one of the following:

a. To keep the solution values on the worksheet, in the Solver Results dialog
box, click Keep Solver Solution.

b. To restore the original values before you clicked Solve, click Restore Original
Values.

c. You can interrupt the solution process by pressing Esc. Excel recalculates the
worksheet with the last values that are found for the decision variable cells.

d. To create a report that is based on your solution after Solver finds a solution,
you can click a report type in the Reports box and then click OK. The report is
created on a new worksheet in your workbook. If Solver doesn't find a solution,
only certain reports or no reports are available.

e. To save your decision variable cell values as a scenario that you can display
later, click Save Scenario in the Solver Results dialog box, and then type a
name for the scenario in
the Scenario Name box.

Step through Solver trial solution

1. After you define a problem, click Options in the Solver Parameters dialog box.

2. In the Options dialog box, select the Show Iteration Results check box to see the values of
each trial solution, and then click OK.

3. In the Solver Parameters dialog box, click Solve.

4. In the Show Trial Solution dialog box, do one of the following:

 To stop the solution process and display the Solver Results dialog box, click Stop.

 To continue the solution process and display the next trial solution, click Continue.

BMB 251/ 2024-25

messages.downloaded_
Change how Solver finds solutions
1. In the Solver Parameters dialog box, click Options.

2. Choose or enter values for any of the options on the All Methods, GRG
Nonlinear, and Evolutionary tabs in the dialog box.

Save or load a problem model

1. In the Solver Parameters dialog box, click Load/Save.

2. Enter a cell range for the model area, and click either Save or Load.

When you save a model, enter the reference for the first cell of a vertical range of empty cells
in which you want to place the problem model. When you load a model, enter the reference
for the entire range of cells that contains the problem model.

Solving methods used by Solver

You can choose any of the following three algorithms or solving methods in the Solver
Parameters dialog box:

 Generalized Reduced Gradient (GRG) Nonlinear Use for problems that are smooth nonlinear.

 LP Simplex Use for problems that are linear.

 Evolutionary Use for problems that are non-smooth.

BMB 251/ 2024-25

messages.downloaded_
SCENARIOS
A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You
can create and save different groups of values as scenarios and then switch between these scenarios to
view the different results.

If several people have specific information that you want to use in scenarios, you can collect the
information in separate workbooks, and then merge the scenarios from the different workbooks into
one.

After you have all the scenarios you need, you can create a scenario summary report that includes
information from all the scenarios.

Scenarios are managed with the Scenario Manager wizard from the What-If Analysis group on the Data
tab.

Creating Scenarios
Suppose that you want to create a budget but are uncertain of your revenue. By using scenarios,
you can define different possible values for the revenue and then switch between scenarios to
perform what-if analyses.

For example, assume that your worst case budget scenario is Gross Revenue of $50,000 and Costs
of Goods Sold of $13,200, leaving $36,800 in Gross Profit. To define this set of values as a scenario,
you first enter the values in a worksheet, as shown in the following illustration:

The Changing cells have values that you type in, while the Result cell contains a formula that is based
on the Changing cells (in this illustration cell B4 has the formula =B2-B3).

You then use the Scenario Manager dialog to save these values as a scenario. Goto the Data tab
> What-If Analysis > Scenario Manager > Add.

BMB 251/ 2024-25

messages.downloaded_
In the Scenario name dialog, name the scenario Worst Case, and specify that cells B2 and B3 are the
values that change between scenarios. If you select the Changing cells on your worksheet before
adding a Scenario, the Scenario Manager will automatically insert the cells for you, otherwise you can
type them by hand, or use the cell selection dialog to the right of the Changing cells dialog box.

BMB 251/ 2024-25

messages.downloaded_
Protection – You can also protect your scenarios, so in the Protection section check the options that
you want, or uncheck them if you don't want any protection.

 Select Prevent Changes to prevent editing the scenario when the worksheet is protected.

 Select Hidden to prevent displaying the scenario when the worksheet is protected.

Now suppose that your Best Case budget scenario is Gross Revenue of $150,000 and Costs of Goods
Sold of $26,000, leaving $124,000 in Gross Profit. To define this set of values as a scenario, you create
another scenario, name it Best Case, and supply different values for cell B2 (150,000) and cell B3
(26,000). Because Gross Profit (cell B4) is a formula - the difference between Revenue (B2) and Costs
(B3) - you do not change cell B4 for the Best Case scenario.

BMB 251/ 2024-25

messages.downloaded_
After you save a scenario, it becomes available in the list of scenarios that you can use in your what-
if analyses. Given the values in the preceding illustration, if you chose to display the Best Case
scenario, the values in the worksheet would change to resemble the following illustration:

Creating A Scenario Summary Report

To compare several scenarios, you can create a report that summarizes them on the same page. The
report can list the scenarios side by side or present them in a PivotTable report.

BMB 251/ 2024-25

messages.downloaded_
A scenario summary report based on the preceding two example scenarios would look something like
the following:

You'll notice that Excel has automatically added Grouping levels for you, which will expand and
collapse the view as you click the different selectors.

A note appears at the end of the summary report explaining that the Current Values column
represents the values of changing cells at the time the Scenario Summary Report was created, and that
the cells that changed for each scenario are highlighted in gray.

Notes:

BMB 251/ 2024-25

messages.downloaded_
  By default, the summary report uses cell references to identify the Changing cells and Result
cells. If you create named ranges for the cells before you run the summary report, the
report will contain the names instead of cell references.

 Scenario reports do not automatically recalculate. If you change the values of a scenario,
those changes will not show up in an existing summary report, but will show up if you create a
new summary report.

 You don't need result cells to generate a scenario summary report, but you do need them for
a scenario PivotTable report.

BMB 251/ 2024-25

messages.downloaded_
VALIDATING AND AUDITING

Apply Data Validation To Cells

Use data validation to restrict the type of data or the values that users enter into a cell, like a
dropdown list.

Select the cell(s) you want to create a rule for.

1. Select Data >Data Validation.

2. On the Settings tab, under Allow, select an option:

 Whole Number - to restrict the cell to accept only whole numbers.
 Decimal - to restrict the cell to accept only decimal numbers.
 List - to pick data from the drop-down list.
 Date - to restrict the cell to accept only date.
 Time - to restrict the cell to accept only time.
 Text Length - to restrict the length of the text.
 Custom – for custom formula.
3. Under Data, select a condition.
4. Set the other required values based on what you chose for Allow and Data.
5. Select the Input Message tab and customize a message users will see when entering data.
6. Select the Show input message when cell is selected checkbox to display the message
when the user selects or hovers over the selected cell(s).
7. Select the Error Alert tab to customize the error message and to choose a Style.
8. Select OK.

Now, if the user tries to enter a value that is not valid, an Error Alert appears with your customized
message.

Auditing Using Trace Precedents and Dependents

Auditing tools in Excel help users trace the origin and impact of data through formula relationships.
These are vital when verifying calculated results or workflows.

Checking formulas for accuracy or finding the source of an error may be difficult if formula uses
precedent or dependent cells:

BMB 251/ 2024-25

messages.downloaded_
  Precedent cells — cells that are referred to by a formula in another cell. For example, if
cell D10 contains the formula =B5, then cell B5 is a precedent to cell D10.

 Dependent cells — these cells contain formulas that refer to other cells. For example, if
cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.

To assist you in checking your formulas, you can use the Trace Precedents and Trace
Dependents commands to graphically display and trace the relationships between these cells and
formulas with tracer arrows, as shown in this figure.

Follow these steps to display formula relationships among cells:

1. Click File > Options > Advanced.

2. In the Display options for this workbook section, select the workbook and then
check that All is chosen in For objects, show.

3. To specify reference cells in another workbook, that workbook must be open. Microsoft
Office Excel cannot go to a cell in a workbook that is not open.

4. Do one of the following.

Trace cells that provide data to a formula (Precedents)

Follow these steps:

1. Select the cell that contains the formula for which you want to find precedent cells.

2. To display a tracer arrow to each cell that directly provides data to the active cell,
on the Formulas tab, in the Formula Auditing group, click Trace Precedents .

 Blue arrows show cells with no errors. Red arrows show cells that cause errors. If the
selected cell is referenced by a cell on another worksheet or workbook, a black
arrow
points from the selected cell to a worksheet icon . The other workbook must be
open before Excel can trace these dependencies.

3. To identify the next level of cells that provide data to the active cell, click
Trace Precedents again.

4. To remove tracer arrows one level at a time, begin with the precedent cell furthest away
from the active cell. Then, on the Formulas tab, in the Formula Auditing group, click the
arrow next to Remove Arrows, and then click Remove Precedent Arrows . To remove
another level of tracer arrows, click the button again.

BMB 251/ 2024-25

messages.downloaded_
Trace formulas that reference a particular cell (Dependents)
Follow these steps:

1. Select the cell for which you want to identify the dependent cells.

2. To display a tracer arrow to each cell that is dependent on the active cell, on the Formulas
tab, in the Formula Auditing group, click Trace Dependents .

Blue arrows show cells with no errors. Red arrows show cells that cause errors. If the selected cell is
referenced by a cell on another worksheet or workbook, a black arrow points from the selected cell
to a worksheet icon . The other workbook must be open before Excel can trace these
dependencies.

3. To identify the next level of cells that depend on the active cell, click
Trace Dependents again.

4. To remove tracer arrows one level at a time, starting with the dependent cell farthest away
from the active cell, on the Formulas tab, in the Formula Auditing group, click the arrow
next
to Remove Arrows, and then click Remove Dependent Arrows . To remove another level of
tracer arrows, click the button again.

See all the relationships on a worksheet

Follow these steps:

1. In an empty cell, enter = (the equal sign).

2. Click the Select All button.

3. Select the cell, and on the Formulas tab, in the Formula Auditing group, click
Trace Precedents twice

To remove all tracer arrows on the worksheet, on the Formulas tab, in the Formula Auditing group,
click Remove Arrows .

BMB 251/ 2024-25

messages.downloaded_
CREATING APPLICATIONS IN SPREADSHEET AND MACROS
Macros in Excel are essentially a sequence of instructions that automate repetitive tasks within Excel.
Instead of performing the same set of actions over and over again, you can set up a macro that does
this for you.

When you assign a macro to a button in Excel, it works kind of like a shortcut key. Just press the button
and your automated task gets done.

The biggest benefit of macros in Excel? You don’t need to be an expert with any programming
language. The macro automatically records what you do, then it duplicates your actions to automate
repetitive tasks.

How to Make Excel Macros

These instructions can be activated either by a button you create or directly from the Developer tab.

So where’s the developer tab? What if you don’t see it in the ribbon?

No problem. I’ll explain step-by-step below.

Enabling the Developer Tab

The first thing you need to do to create macros is to make sure the Developer tab is enabled in your
Excel workbooks. Here’s how you can do that:

1. Go to File.

2. Select Options.

3. Under Customize Ribbon, make sure the Developer tab is

checked. This will allow you access to the macro recording and editing

features.

Let’s start by adding a button that sorts your data. I’ll use the same example I have in my video
(below). It shows a dozen lines of sales data, and we want to sort it so the highest amounts show up at
the top.

messages.downloaded_
BMB 251/ 2024-25

messages.downloaded_
Creating a Simple Sorting Macro

Here’s a demonstration you can follow along with. Note that it also appears in my video (below) in case
you want to watch it happen. You don’t need to use a visual basic editor to create these macros.
Instead, you’ll just make a few mouse clicks!

Step 1: Preparing Your Data

Let’s begin with some raw sales data. Assume you receive this data every few days and want to
automate the sorting of this data from highest to lowest.

Step 2: Recording the Macro

1. Go to the Developer tab in your toolbar.

2. Click Record Macro.

3. Enter the name of the macro (e.g., “Sort”) in the macro dialogue box, then click OK.

Once you hit OK, Excel starts recording every action you perform. Think of it as the “record macro
button”. When you click it, it starts recording every keystroke and action you take in Microsoft Excel.

When you click “stop recording”, your macro is finished.

So here’s what we want to do for the sorting task:

Step 3: Performing the Sorting Task

1. Go to the Data tab.

2. Select Sort and choose to sort by the desired column (e.g., amount) from largest to smallest.

3. Click OK to sort your data.

Step 4: Stopping the Recording

Return to the Developer tab and click Stop Recording. Your macro is now saved.

Running Your Macro

BMB 251/ 2024-25

messages.downloaded_
To run the macro:

1. Go to the Developer tab.

2. Click Macros.

3. Select the macro you recorded (e.g., “Sort”) and click Run.

Creating a Button for Your Macro

You can make running your macro even easier by creating a button:

1. Insert a shape (e.g., a rectangle) and label it (e.g., “Sort”).

2. Right-click the shape and select Assign Macro.

3. Choose the macro you created (e.g., “Sort”).

4. Now, whenever you click this shape, your data will be sorted automatically.

Creating a Formatting Macro in Excel

Step 1: Recording the Macro

1. Return to the Developer tab.

2. Click Record Macro and name it (e.g., “Format”) in the Macro name box.

3. Click OK to start recording.

Step 2: Formatting Your Data

1. Delete any unnecessary data.

2. Change header colors (e.g., make headers blue and text white).

3. Add borders to the cells.

4. Highlight important data.

Step 3: Stopping the Recording

Return to the Developer tab and click Stop Recording. Your formatting macro is now ready.

BMB 251/ 2024-25

messages.downloaded_
Assigning a Button to the Formatting Macro

1. Insert another shape and label it (e.g., “Format”).

2. Right-click this shape and select Assign Macro.

3. Choose the macro you recorded (e.g., “Format”).

Now, whenever you press this button, your data will be formatted as you’ve specified.

Combining Macros in Excel

Though we’ve created separate macros for sorting and formatting, you can combine these tasks into a
single macro. Doing so can be useful if you want to automate more complex workflows.

As an example, you may want to consider using a macro to remove dashes. That’s a type of formatting,
but it’s more complex than just adding a color. When you make it a macro, you only need to click one
button to complete the task on every worksheet in your workbook.

Benefits of Macros in Excel

The biggest benefit of Excel macros? Time savings.

Anytime you find yourself repeating a task in your workbook, consider adding an automation. You
can do this, as I mentioned above, by clicking the record button and performing your tasks a single
time. Then, click “stop recording” and set up your macro.

You can now use it anytime you want to repeat that task. . . regardless of how complex it is.

BMB 251/ 2024-25

messages.downloaded_
CREATING AND FORMATTING CHARTS
Charts help you visualize your data in a way that creates maximum impact on your audience. Learn to
create a chart and add a trendline. You can start your document from a recommended chart or
choose one from our collection of pre-built chart templates.

Create a chart
1. Select data for the chart.

2. Select Insert > Recommended Charts.

3. Select a chart on the Recommended Charts tab, to preview the chart.

4. Select a chart.

5. Select OK.

Add a trendline
1. Select a chart.

2. Select Chart Design > Add Chart Element.

3. Select Trendline and then select the type of trendline you want,
such as Linear, Exponential, Linear Forecast, or Moving Average.

Understanding Chart Types

When you create a chart in an Excel worksheet, a Word document, or a PowerPoint presentation, you
have a lot of options. Whether you’ll use a chart that’s recommended for your data, one that you’ll
pick from the list of all charts, or one from our selection of chart templates, it might help to know a
little more about each type of chart.

1. Column Chart
Data that’s arranged in columns or rows on a worksheet can be plotted in a column chart. A
column chart typically displays categories along the horizontal (category) axis and values along
the vertical (value) axis, as shown in this chart:

BMB 251/ 2024-25

messages.downloaded_
 2. Bar chart
Data that's arranged in columns or rows on a worksheet can be plotted in a bar chart. Bar
charts illustrate comparisons among individual items. In a bar chart, the categories are typically
organized along the vertical axis, and the values along the horizontal axis.

Consider using a bar chart when:

 The axis labels are long.

 The values that are shown are durations.

3. Line chart
Data that's arranged in columns or rows on a worksheet can be plotted in a line chart. In a line
chart, category data is distributed evenly along the horizontal axis, and all value data is
distributed evenly along the vertical axis. Line charts can show continuous data over time on an
evenly scaled axis, so they're ideal for showing trends in data at equal intervals, like months,
quarters, or fiscal years.

BMB 251/ 2024-25

messages.downloaded_
 4. Pie chart
Data that's arranged in one column or row on a worksheet can be plotted in a pie chart. Pie
charts show the size of items in one data series, proportional to the sum of the items. The data
points in a pie chart are shown as a percentage of the whole pie.

Consider using a pie chart when:

 You have only one data series.

 None of the values in your data are negative.

 Almost none of the values in your data are zero values.

 You have no more than seven categories, all of which represent parts of the whole pie.

5. XY scatter chart
Data that's arranged in columns and rows on a worksheet can be plotted in an xy (scatter)
chart. Place the x values in one row or column, and then enter the corresponding y values in
the adjacent rows or columns.
BMB 251/ 2024-25

messages.downloaded_
 A scatter chart has two value axes: a horizontal (x) and a vertical (y) value axis. It combines x
and y values into single data points and shows them in irregular intervals, or clusters. Scatter
charts are typically used for showing and comparing numeric values, like scientific,
statistical, and engineering data.

Consider using a scatter chart when:

 You want to change the scale of the horizontal axis.

 You want to make that axis a logarithmic scale.

 Values for horizontal axis are not evenly spaced.

 There are many data points on the horizontal axis.

 You want to adjust the independent axis scales of a scatter chart to reveal more
information about data that includes pairs or grouped sets of values.

 You want to show similarities between large sets of data instead of differences
between data points.

 You want to compare many data points without regard to time—the more data that
you include in a scatter chart, the better the comparisons you can make.

6. Area chart
Data that's arranged in columns or rows on a worksheet can be plotted in an area chart. Area
charts can be used to plot change over time and draw attention to the total value across a
trend. By showing the sum of the plotted values, an area chart also shows the relationship of
parts to a whole.

BMB 251/ 2024-25

messages.downloaded_
 7. Surface chart
Data that's arranged in columns or rows on a worksheet can be plotted in a surface chart. This
chart is useful when you want to find optimum combinations between two sets of data. As in a
topographic map, colors and patterns indicate areas that are in the same range of values. You
can create a surface chart when both categories and data series are numeric values.

BMB 251/ 2024-25

messages.downloaded_
 8. Bubble chart
Much like a scatter chart, a bubble chart adds a third column to specify the size of the bubbles
it shows to represent the data points in the data series.

Combined Chart
A Combined Chart (also called a Combo Chart) in Microsoft Excel is a graphical representation that
combines two or more different chart types into a single chart. This feature is particularly useful
for visualizing multiple types of data with different value ranges or units in the same graph.

Combo charts are commonly used in financial reports, academic research, and business
dashboards where comparative analysis is essential.

Steps to Create a Combined Chart

1. Enter Data: Create a table with at least two different data series.

2. Select Data: Highlight the entire dataset (including headers).

3. Insert Chart:

o Go to the Insert tab.

o Click on the drop-down under Insert Combo Chart.

o Choose Custom Combo Chart.

Column and line chart

A Column and Line Chart is a type of combo chart that combines column bars and line graphs in a
single visual. It is commonly used when comparing two different types of data where one dataset is
best represented as bars and the other as a line.

BMB 251/ 2024-25

messages.downloaded_
Column and area chart
A Column and Area Chart is another variant of a combo chart that combines vertical bars with
shaded area charts. This type of chart helps in visualizing individual values (columns) along with
cumulative or trend-based data (area).

Chart Customization in Excel

Changing the Chart Type for a Defined Data Series

In Microsoft Excel, a data series is a group of related data points plotted in a chart. You can change
the chart type for any individual series to improve clarity or suit different data visualization needs.

Example Scenario:

You have a combo chart with monthly revenue and profit percentage. Revenue is shown as columns,
but you wish to change profit percentage from a column to a line chart.

Steps:

1. Select the chart.

2. Right-click on the specific data series (e.g., Profit %).

3. Choose "Change Series Chart Type".

BMB 251/ 2024-25

messages.downloaded_
 4. In the dialog box, select a different chart type (e.g., Line).

5. Click OK.

This allows you to combine different chart types (e.g., Column + Line) within the same chart area.

Add or Delete a Data Series in a Chart

Excel allows users to add more datasets to an existing chart or remove unnecessary ones.

To Add a Data Series:

1. Click the chart.

2. Go to the Chart Tools Design tab and click Select Data.

3. In the Select Data Source window, click Add.

4. Enter the name of the new series and its values.

5. Click OK to apply changes.

To Delete a Data Series:

1. Select the chart and go to Select Data again.

2. Under Legend Entries (Series), choose the series to remove.

3. Click Remove and then OK.

This feature is helpful when updating charts with newly added rows or columns in your dataset.

Reposition Chart Title, Legend, and Data Labels

Chart Title:

 Move: Click the chart title and drag it to a new location (e.g., center-top).

 Format: Right-click the title → Format Chart Title to change font, alignment, or effects.

Legend:

 Reposition:

o Click the legend.

o Go to Chart Elements (+ icon) → Legend → choose position: Right, Top, Bottom, Left.

BMB 251/ 2024-25

messages.downloaded_
  Manual Move: Drag the legend box to a custom location within the chart area.

Data Labels:

 Add: Click the chart → click + (Chart Elements) → check Data Labels.

 Format:

o Right-click the data label → Format Data Labels.

o Choose location: Inside End, Outside End, Center, etc.

o You can also display additional values like percentages or series names.

Example Use Case:

In a lab performance chart, you can:

 Show the student’s marks as columns.

 Add data labels on top of each bar.

 Place the legend on the right side.

 Move the chart title to the center-top for better visibility.

Change scale of value axis

1. Change the scale of the vertical (value) axis in a chart

By default, Excel determines the minimum and maximum scale values of the vertical (value) axis,
also known as the y axis, when you create a chart. However, you can customize the scale to better
meet your needs. When the values that are plotted in the chart cover a very large range, you can
also change the value axis to a logarithmic scale, also known as log scale.

1. In a chart, click the value axis that you want to change, or do the following to select the
axis from a list of chart elements:

a. Click anywhere in the chart.

This displays the Chart Tools, adding the Design and Format tabs.

b. On the Format tab, in the Current Selection group, click the arrow next to the
Chart Elements box, and then click Vertical (Value) Axis.

BMB 251/ 2024-25

messages.downloaded_
 2. On the Format tab, in the Current Selection group, click Format Selection.

3. In the Format Axis pane, click Axis Options, and then do one or more of the following:

a. To change the number at which the vertical (value) axis starts or ends, for
the Minimum or Maximum option, type a different number in the Minimum box or
the Maximum box. You can click Reset to bring it back to its original value if needed.

b. To change the interval of tick marks and chart gridlines, for the Major unit or Minor
unit option, type a different number in the Major unit box or Minor unit box. You
can click Reset to bring it back to its original value if needed.

c. To reverse the order of the values, select the Values in reverse order check box.

d. To change the value axis to logarithmic, select the Logarithmic scale check box.

e. To change the display units on the value axis, in the Display units list, select the
units you want.

To show a label that describes the units, select the Show display units label on
chart check box.

f. To change the placement of the axis tick marks and labels, under Tick Marks, select
any of the options in the Major type or Minor type boxes.

g. Click the drop down box under Labels and choose a label position.

h. To change the point where you want the horizontal (category) axis to cross the vertical
(value) axis, under Floor crosses at, click Axis value, and then type the number you
want in the text box. Or, click Maximum axis value to specify that the horizontal
(category) axis crosses the vertical (value) axis at the highest value on the axis.

BMB 251/ 2024-25

messages.downloaded_
2. Change the scale of the horizontal (category) axis in a chart
The horizontal (category) axis, also known as the x axis, of a chart displays text labels instead of
numeric intervals and provides fewer scaling options than are available for a vertical (value) axis, also
known as the y axis, of the chart.

However, you can specify the following axis options:

 Interval between tick marks and labels

 Placement of labels

 Order in which categories are displayed

 Axis type (date or text axis)

BMB 251/ 2024-25

messages.downloaded_
  Placement of tick marks

 Point where the horizontal axis crosses the vertical axis

1. In a chart, click to select the category axis that you want to change, or do the following to
select the axis from a list of chart elements:

a. Click anywhere in the chart.

This displays the Chart Tools, adding the Design and Format tabs.

b. On the Format tab, in the Current Selection group, click the arrow in the box at the
top, and then click Horizontal (Category) Axis.

2. On the Format tab, in the Current Selection group, click Format Selection.

3. In the Format Axis pane, do any of the following:

 To reverse the order of categories, expand Axis Options, and then select the
Categories in reverse order check box.

 To change the axis type to a text or date axis, expand Axis Options, and then under
Axis Type, select Text axis or Date axis. Text and data points are evenly spaced on a
text axis. A date axis displays dates in chronological order at set intervals or base units,
such as the number of days, months or years, even if the dates on the worksheet are
not in
order or in the same base units.

 To change the point where you want the vertical (value) axis to cross the horizontal
(category) axis, expand Axis Options, and then under Vertical axis crosses, select
At category number and type the number that you want in the text box, or select
At maximum category to specify that the vertical (value) axis cross the horizontal
(category) axis after the last category on the x-axis.

 To change the interval between tick marks, expand Tick Marks, and then in the
Interval between tick marks box, type the number that you want.

 To change the placement of the axis tick marks, expand Tick Marks, and then select
any of the options that you want in the Major type and Minor type boxes.

 To change the interval between axis labels, expand Labels, and then under Interval
between labels, select Specify interval unit and type the number you want in the
text box.

 To change the placement of axis labels, expand Labels, and then in the Distance
from axis box, type the number that you want.

BMB 251/ 2024-25

messages.downloaded_
Format elements of a chart
You can change the format of individual chart elements, such as the chart area, plot area, data series,
axes, titles, data labels, or legend.

Two sets of tools are available for formatting chart elements: the Format task pane and the Chart Tools
Ribbon. For the most control, use the options in the Format task pane.

Format your chart using the Format task pane

Select the chart element (for example, data series, axes, or titles), right-click it, and click Format
<chart element>. The Format pane appears with options that are tailored for the selected chart
element.

Clicking the small icons at the top of the pane moves you to other parts of the pane with more
options. If you click on a different chart element, you’ll see that the task pane automatically updates
to the new chart element.

For example, to format an axis:

1. Right-click the chart axis, and click Format Axis.

2. In the Format Axis task pane, make the changes you want.

You can move or resize the task pane to make working with it easier. Click the chevron in the
upper right.

BMB 251/ 2024-25

messages.downloaded_
  Select Move and then drag the pane to a new location.

 Select Size and drag the edge of the pane to resize it.

Format your chart using the Ribbon

1. In your chart, click to select the chart element that you want to format.

2. On the Format tab under Chart Tools, do one of the following:

 Click Shape Fill to apply a different fill color, or a gradient, picture, or texture to
the chart element.

 Click Shape Outline to change the color, weight, or style of the chart element.

 Click Shape Effects to apply special visual effects to the chart element, such as
shadows, bevels, or 3-D rotation.

 To apply a predefined shape style, on the Format tab, in the Shape Styles group,
click the style that you want. To see all available shape styles, click the More button
.

 To change the format of chart text, select the text, and then choose an option on the
mini toolbar that appears. Or, on the Home tab, in the Font group, select the
formatting that you want to use.

 To use WordArt styles to format text, select the text, and then on the Format tab in
the WordArt Styles group, choose a WordArt style to apply. To see all available
styles, click the More button .

BMB 251/ 2024-25

messages.downloaded_
Use a picture in a chart
You can enhance a chart by inserting a picture (such as a business logo) in the chart area. Or you can
use a picture fill to draw attention to specific chart elements such as the chart area, the plot area, the
data markers (in column, bar, area, bubble, 3-D line, and filled radar charts), the legend (in 2-D and 3-D
charts), or the 3-D walls and floor of a chart.

You can also copy a picture to a chart or to chart elements such as data markers in 2-D line charts,
scatter charts, or an unfilled radar chart. If a picture is no longer needed, you can remove it.

Insert a picture in a chart

1. Click the chart area of the chart.

2. On the Insert tab, in the Illustrations group, click Pictures.

3. Locate the picture that you want to insert, and then double-click it.

4. To change the size of the picture, drag a sizing handle away from or toward the center of
the picture while doing one of the following:

 To keep the center of the picture in the same place, hold down CTRL while you drag
the sizing handle.

 To maintain the proportions of the picture, hold down SHIFT while you drag the
sizing handle.

 To both keep the center of a picture in the same place and maintain its
proportions, hold down CTRL+SHIFT while you drag the sizing handle.

You can also resize a picture to an exact height and width by selecting the picture and
then entering the size that you want in the Shape Height and Shape Width boxes
(Format tab, Size group).

5. To move the picture, drag it to the location that you want.

BMB 251/ 2024-25

messages.downloaded_
BMB 251/ 2024-25

messages.downloaded_
DATABASE MANAGEMENT SYSTEM
A Database Management System (DBMS) is software that interacts with users, applications, and the
database itself to capture and analyze data. In lab-based learning, practical exposure to creating
tables, views, generating reports, and executing SQL queries provides a foundation for building real-
world database-driven applications.

1. Creation of Table
A table is a collection of related data held in a structured format within a database. It consists of rows
and columns.

SQL Syntax:

CREATE TABLE Students (

StudentID INT PRIMARY

KEY, Name VARCHAR(50),

Age INT,

Course VARCHAR(30)

);

Steps in DBMS Tools (e.g., MySQL, Oracle, MS Access):

1. Open the DBMS application.

2. Choose/Create a database.

3. Use SQL editor or design view to create a new table.

4. Define column names, data types, and primary keys.

5. Save the table.

2. Creation of Views
A view is a virtual table created by a query that pulls data from one or more tables. It does not store
data itself but provides a dynamic result set.

SQL Syntax:

CREATE VIEW StudentView AS

SELECT Name, Course

FROM Students

WHERE Age > 20;

Use Case:

BMB 251/ 2024-25

messages.downloaded_
Views are useful for:

 Simplifying complex queries.

 Providing data access control.

 Creating customized data representations.

3. Generating Reports
Reports are formatted outputs that summarize and present data in a readable and organized way. In
DBMS like MS Access, reports are created using GUI tools.

Steps to Create Reports (MS Access or Similar Tools):

1. Choose Create → Report Wizard.

2. Select the table or query as data source.

3. Choose fields to display.

4. Group, sort, and format the report layout.

5. Save and preview the report.

In SQL-based reporting tools:

Use SELECT queries and connect them with report designers like Crystal Reports, Power BI, or Access
Reports.

4. Basics of SQL
Structured Query Language (SQL) is the standard language for interacting with relational databases.

BMB 251/ 2024-25

messages.downloaded_
Key SQL Commands:

1. Data Definition Language (DDL)

DDL or Data Definition Language actually consists of the SQL commands that can be used
for defining, altering, and deleting database structures such as tables, indexes, and schemas. It
simply deals with descriptions of the database schema and is used to create and modify the
structure of database objects in the database.

Common DDL Commands

Command Description Syntax

Create database or its objects

CREATE TABLE table_name (column1
CREATE (table, index, function, views,
data_type, column2 data_type, ...);
store procedure, and triggers)

Delete objects from the

DROP DROP TABLE table_name;
database

Alter the structure of the ALTER TABLE table_name ADD

ALTER
database COLUMN column_name
data_type;

BMB 251/ 2024-25

messages.downloaded_
 Command Description Syntax

Remove all records from a

table, including all spaces
TRUNCATE TRUNCATE TABLE table_name;
allocated for the records are
removed

Add comments to the data COMMENT 'comment_text' ON

COMMENT
dictionary TABLE table_name;

Rename an object existing in RENAME TABLE old_table_name

RENAME
the database TO new_table_name;

2. Data Manipulation Language (DML)

The SQL commands that deal with the manipulation of data present in the database belong
to DML or Data Manipulation Language and this includes most of the SQL statements. It is
the
component of the SQL statement that controls access to data and to the database. Basically, DCL
statements are grouped with DML statements.

Common DML Commands

Command Description Syntax

Insert data into a INSERT INTO table_name (column1, column2, ...)

INSERT
table VALUES (value1, value2, ...);

Update existing UPDATE table_name SET column1 = value1,

UPDATE
data within a table column2 = value2 WHERE condition;

Delete records
DELETE DELETE FROM table_name WHERE condition;
from a database
table

Table control
LOCK LOCK TABLE table_name IN lock_mode;
concurrency

BMB 251/ 2024-25

messages.downloaded_
 Command Description Syntax

Call a PL/SQL or
CALL CALL procedure_name(arguments);
JAVA subprogram

EXPLAIN Describe the access

EXPLAIN PLAN FOR SELECT * FROM table_name;
PLAN path to data

3. Data Query Language (DQL)

DQL statements are used for performing queries on the data within schema objects. The purpose
of the DQL Command is to get some schema relation based on the query passed to it. This
command allows getting the data out of the database to perform operations with it. When
a SELECT is fired against a table or tables the result is compiled into a further temporary table,
which is displayed or perhaps received by the program.

DQL Command

Command Description Syntax

It is used to retrieve data from SELECT column1, column2, ...FROM

SELECT
the database table_name WHERE condition;

4. Data Control Language (DCL)

DCL (Data Control Language) includes commands such as GRANT and REVOKE which mainly deal
with the rights, permissions, and other controls of the database system. These commands are
used to control access to data in the database by granting or revoking permissions.

Common DCL Commands

Command Description Syntax

Assigns new privileges to a user

GRANT privilege_type [(column_list)]
account, allowing access to
GRANT ON [object_type] object_name TO
specific database objects,
user [WITH GRANT OPTION];
actions, or functions.

BMB 251/ 2024-25

messages.downloaded_
 Command Description Syntax

Removes previously granted

REVOKE [GRANT OPTION FOR]
privileges from a user account,
privilege_type [(column_list)] ON
REVOKE taking away their access to
[object_type] object_name FROM user
certain database objects or
[CASCADE];
actions.

5. TCL - Transaction Control Language

Transactions group a set of tasks into a single execution unit. Each transaction begins with a
specific task and ends when all the tasks in the group are successfully completed. If any of the tasks
fail, the transaction fails. Therefore, a transaction has only two results: success or failure. We can
explore more about transactions here.

Common TCL Commands

Command Description Syntax

BEGIN BEGIN TRANSACTION

Starts a new transaction
TRANSACTION [transaction_name];

Saves all changes made during

COMMIT COMMIT;
the transaction

Undoes all changes made

ROLLBACK ROLLBACK;
during the transaction

Creates a savepoint within the

SAVEPOINT SAVEPOINT savepoint_name;
current transaction

Running Queries
Running queries allows retrieval and manipulation of data from the database.

Steps:

1. Open the SQL query editor.

BMB 251/ 2024-25

messages.downloaded_
 2. Write the SQL command.

3. Click Run or Execute.

4. View results in the result pane or output grid.

Example Queries:

 Retrieve all student records

SELECT * FROM Students;

 Find students enrolled in MBA

SELECT Name FROM Students WHERE Course = 'MBA';

BMB 251/ 2024-25

messages.downloaded_