CONTENT

S.N EXPERIMENT/TOPIC DATE SIGNATURE/

O REMARKS
1. PIVOT TABLE

2. ANALYZING DATA USING GOAL

SEEK AND SOLVER

3. SCENARIOS CREATE NAMED

SCENARIOS

4. IDENTIFY CELLS WITH MISSING

DEPENDENTS

5. CREATING APPLICATION IN
SPREADSHEET AND MACROS

6. CREATING AND FORMATTING

CHARTS

7. UNDERSTANDING CHART TYPES

8. XY SCATTER CHART

9. AREA CHART

10. SURFACE CHART

11. BUBBLE CHART

  Pivot table:-
A PivotTable in Excel is a dynamic way to summarize and analyze data by grouping and
summarizing values from a larger table. It allows you to quickly create reports and dashboards
without writing formulas, and the results are interactive, meaning you can easily adjust the layout
and analysis by simply dragging and dropping fields.

Key Features and Benefits:

 Data Summarization:
PivotTables can automatically calculate sums, averages, counts, and other statistics, providing
quick insights into your data.
 Interactive Analysis:
You can easily change the layout, filters, and calculations of the PivotTable by dragging and
dropping fields into different areas (Rows, Columns, Values, Filters).
 Customizable Reports:
You can create a variety of reports and dashboards based on your data, making it easy to present
findings to others.
 No Formulas Needed:
PivotTables handle the calculations for you, so you don't need to write complex formulas.
 Fast Analysis:
They are designed for quickly analyzing large datasets and answering unanticipated questions
about your data.

How to Create a PivotTable:

1. Select Data: Highlight the range of cells containing your data, or a named table.
2. Insert PivotTable: Go to the Insert tab on the Excel ribbon and click PivotTable.
3. Choose Location: Select whether you want the PivotTable in a new worksheet or an existing
one, and specify the cell where you want it placed.
4. Create PivotTable: Click OK, and Excel will create a blank PivotTable and display the
PivotTable Fields list.
5. Drag and Drop Fields: Drag the fields you want to analyze into the Rows, Columns, Values,
and Filters areas of the PivotTable.
6. Customize: Adjust the layout, filters, and calculations to get the desired report.
Example:
Imagine you have a sales report with data on products, regions, and sales figures. You can use a
PivotTable to quickly summarize sales by region and product, or to filter sales by specific
regions or products.

In Summary:
PivotTables are a powerful and versatile tool for summarizing and analyzing data in Excel. They
are easy to use, interactive, and can save you a lot of time and effort when creating reports and
dashboards

 Analyzing data using goal seek and solver:-

Goal Seek and Solver are two powerful tools in Excel used for data analysis, especially for
what-if scenarios, optimization, and finding unknown variables that meet specific goals.

📌 Goal Seek Overview

Goal Seek is best used when you know the desired result of a formula, and you want to find the
input value that achieves it.

✅ Use Case:

 Simple one-variable equations (e.g., What interest rate is needed to reach a future value
of $10,000 in 5 years?)

🎯 How to Use Goal Seek in Excel:

1. Set up a formula. (Example: =A1*A2 in cell A3)

2. Go to Data > What-If Analysis > Goal Seek
3. Set:
o Set Cell: the cell with the formula (e.g., A3)
o To Value: the desired result (e.g., 100)
o By Changing Cell: the input cell you want to adjust (e.g., A2)
4. Click OK

📌 Solver Overview

Solver is more advanced and can handle:

  Multiple variables
 Constraints (e.g., variables must be integers or under a limit)
 Optimization problems (maximize, minimize, or set a value)

✅ Use Case:

 Maximizing profit given production limits

 Finding the best investment mix under risk constraints

🎯 How to Use Solver in Excel:

1. Install Solver if it’s not enabled:

File > Options > Add-ins > Excel Add-ins > Check Solver Add-in
2. Set up your spreadsheet with:
o Objective Cell: contains a formula to optimize
o Variable Cells: inputs Solver will change
o Constraints: restrictions on inputs or outcomes
3. Go to Data > Solver
4. Define:
o Set Objective: the cell to optimize
o To: Max, Min, or Value Of
o By Changing Variable Cells
o Add Constraints as needed
5. Choose a solving method:
o Simplex LP for linear problems
o GRG Nonlinear or Evolutionary for complex models
6. Click Solve

✅ Example Comparison:

Feature Goal Seek Solver

Input Cells One Multiple
Target Value Known Can maximize, minimize, or set a value

Constraints None Multiple (e.g., budget, resource limits)

Complexity Simple Advanced

 Scenarios create named Scenarios :-

Creating named scenarios in Excel is part of Scenario Manager, another tool under What-If
Analysis. It lets you model and compare different sets of inputs and see how they affect your
results — very useful for business planning, budgeting, forecasting, and decision-making.

A scenario is a saved set of input values that Excel can plug into your model to show different
outcomes. You can define and switch between different scenarios (e.g., Best Case, Worst Case,
Most Likely) without re-entering data.

🎯 When to Use Scenarios

 Budgeting: Change income and expense estimates.

 Sales Forecasting: Vary unit price and volume.
 Investment Planning: Compare low-risk, medium-risk, and high-risk portfolios.

How to Create Named Scenarios

Step-by-Step:

1. Prepare Your Model

o Set up your spreadsheet with input cells and formulas that calculate a result.
o Example:

ini
CopyEdit
B1 = Price per unit
B2 = Units sold
B3 = =B1 * B2 (Revenue)

2. Open Scenario Manager

o Go to Data > What-If Analysis > Scenario Manager
o Click Add
3. Add a Scenario
o Name your scenario (e.g., Best Case)
o Changing Cells: select the input cells (e.g., B1:B2)
o Click OK
4. Enter Values for This Scenario
o Excel will prompt you to enter the values for the cells you selected
 e.g., B1 = $20, B2 = 1000
o Click OK
5. Repeat for More Scenarios
o Add other scenarios like Worst Case, Expected Case with different values
6. View Scenarios
o In Scenario Manager, select a scenario and click Show
 Excel will update the input cells and calculations accordingly
o
7. Summary Report (Optional)
o In Scenario Manager, click Summary
o Choose:
 Scenario Summary or Scenario PivotTable Report
 Select Result Cells (e.g., B3 for r

🧾 Example:

Scenario Price Units Revenue (B3)

(B1) Sold
(B2)
Best Case $30 1,500 $45,000
Expected $25 1,000 $25,000
Worst $20 700 $14,000
Case

 Identify cells with missing dependents:-

In Excel, "cells with missing dependents" typically means cells that:

 Contain values or formulas but are not referenced by any other cells (i.e., they have no
dependents).
 Could represent redundant or unused data in your workbook.

To Identify Cells with Missing Dependents in Excel:

Method 1: Use “Trace Dependents”

1. Select a cell.
2. Go to the Formulas tab.
3. Click Trace Dependents.
4. If no arrows appear, the cell has no dependents.

You can do this repeatedly for all cells manually, or use a macro for bulk checks.
Method 2: Use a VBA Macro to Find All Cells Without Dependents

Here’s a simple macro to find and highlight all non-empty cells with no dependents:

vba
CopyEdit
Sub FindCellsWithoutDependents()
Dim ws As Worksheet
Dim cell As Range

Set ws = ActiveSheet

[Link] = False
For Each cell In [Link]
If Not IsEmpty(cell) Then
On Error Resume Next
If [Link] Is Nothing Then
[Link] = RGB(255, 255, 0) ' Highlight yellow
End If
On Error GoTo 0
End If
Next cell
[Link] = True

MsgBox "Highlighting complete. Yellow = cells with no dependents."

End Sub

🔧 Tip: Press ALT + F11 to open the VBA editor, insert a new module, paste the code, and run it

Use Cases

 Auditing spreadsheets for unused calculations.

 Cleaning up large models or legacy files.
 Debugging where expected links are missing.

 Creating applications in spread sheet and macros:-

Creating an application in a spreadsheet using macros (typically with Excel + VBA) involves
designing a structured, interactive, and user-friendly experience that can automate tasks, manage
data, and perform calculations — much like a basic software program, but entirely within Excel.
🔧 Step-by-Step Guide to Creating an Application in Excel with Macros

1. Plan the Application

Ask yourself:

 What does the application do? (e.g., inventory tracker, invoice system, expense manager)
 What inputs/outputs are needed?
 What features should be automated?

2. Design the User Interface (UI)

Create clean and organized sheets:

 Use clear labels, tables, drop-downs (via Data Validation).

 Hide gridlines and use forms or shapes as buttons.
 Lock cells the user shouldn't touch (via Sheet Protection).

3. Enable the Developer Tab

To start using macros:

1. Go to File > Options > Customize Ribbon

2. Check Developer
3. Click OK

Now you'll have access to:

 Visual Basic Editor

 Macro Recorder
 Insert buttons, forms, etc.

4. Use VBA (Visual Basic for Applications)

👉 Example: Add a New Record to a Table

vba
CopyEdit
Sub AddRecord()
Dim ws As Worksheet
Dim lastRow As Long

Set ws = Sheets("Data")
lastRow = [Link]([Link], 1).End(xlUp).Row + 1

[Link](lastRow, 1).Value = Sheets("Form").Range("B2").Value ' Name

[Link](lastRow, 2).Value = Sheets("Form").Range("B3").Value ' Date
[Link](lastRow, 3).Value = Sheets("Form").Range("B4").Value ' Amount

MsgBox "Record added successfully!", vbInformation

End Sub

 Sheet "Form" is the input sheet.

 Sheet "Data" is where records are stored.
 Buttons on "Form" can be assigned to this macro.

5. Use Buttons and Forms

Add a Button:

 Go to Developer > Insert > Button (Form Control)

 Draw it on the sheet
 Assign your macro

Optional: Use UserForms

 Create dialog boxes for complex inputs

 Handle validations in code

6. Protect and Distribute

 Use Workbook Protection to restrict access

 Hide formulas and sheets users shouldn't see
 Save as .xlsm (macro-enabled file)

✅ Example Applications You Can Build:

App Type Features

Inventory Management Add/edit items, track stock levels
Invoice Generator Input customer data, auto-generate PDF
 Task Tracker Add tasks, mark complete, color code
Budget Planner Monthly inputs, graphs, summaries
Attendance Register Mark daily presence, auto-summary

 Creating and formatting charts:-

Creating and formatting charts in Excel—either manually or using macros/VBA—is a core part
of building professional, data-driven spreadsheet applications. Here's a complete guide on how to
do both:

PART 1: Manually Creating & Formatting Charts

🔹 Step 1: Insert a Chart

1. Select your data range.

2. Go to Insert > Charts group.
3. Choose a chart type: Column, Line, Pie, etc.

🔹 Step 2: Format the Chart

 Chart Title: Click and rename it.

 Legend: Move or remove from the Chart Elements button (+ icon).
 Axes:
o Right-click axis > Format Axis
o Adjust number format, scale, or labels.
 Colors:
o Select data series > Right-click > Format Data Series.
o Choose fill color, border, or transparency.
 Gridlines: Add/remove from Chart Elements.

Example Use Case:

Sales by Month:

Month Sales

Jan 1200
Month Sales

Feb 1350

Mar 980

Highlight table > Insert > Column Chart.

PART 2: Creating Charts with VBA (Macros)

You can use macros to automatically generate and format charts. This is useful when working
with dynamic or repeating reports.

🔸 Example: Create a Column Chart via VBA

vba
CopyEdit
Sub CreateChart()
Dim ws As Worksheet
Dim chartObj As ChartObject
Dim chartRange As Range

Set ws = Sheets("Sheet1")
Set chartRange = [Link]("A1:B4") ' Adjust as needed

' Add the chart

Set chartObj = [Link](Left:=300, Width:=400, Top:=50,
Height:=300)
With [Link]
.SetSourceData Source:=chartRange
.ChartType = xlColumnClustered
.HasTitle = True
.[Link] = "Monthly Sales"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).[Link] = "Month"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).[Link] = "Sales Amount"
End With
End Sub

🔸 Add to Button

 Insert a button (Developer tab > Insert > Button).

 Assign the Create
 \Chart macro.
🎨 Formatting Options via VBA

 Chart Type: .ChartType = xlLine, xlPie, etc.

 Colors: Format data series with .[Link]
 Legend: .HasLegend = False to hide
 Gridlines: .Axes(xlValue).[Link]

✅ Tips for Professional Charts

 Use consistent color schemes.

 Keep charts simple (avoid 3D unless necessary).
 Add data labels only when useful.
 Use slicers or pivot charts for interactivity.

 Understanding chart types:-

Understanding different chart types in Excel is essential for selecting the right visual
representation of your data. Each chart type serves a specific purpose based on the kind of
comparison, trend, or distribution you want to show.

📊 Common Excel Chart Types Explained

Chart Type Best For Description

Column Chart Comparisons Shows vertical bars to compare values across categories. Useful for
sales, quantities, etc.

Bar Chart Comparisons (long Horizontal version of a column chart; better when labels are long.
labels)

Line Chart Trends over time Displays data points connected by lines. Great for showing changes
over months, years, etc.

Pie Chart Parts of a whole Shows proportions as slices of a circle. Best for showing % of total
(limit to ~5-6 slices).

Doughnut Like Pie Chart Similar to a pie chart but with a hole in the middle. Can show
Chart multiple series.
Area Chart Cumulative trends Similar to line charts but with the area below the line filled. Good
for stacked data.

Scatter Plot Correlation Plots points on X and Y axis to find relationships between two
variables (e.g. height vs. weight).

Bubble Chart 3 Variables Like a scatter plot, but the size of the dot shows a third variable.
Combo Chart Mixed Data Types Combines two chart types (e.g. column + line) for data with
different scales.
Histogram Distribution Groups numbers into ranges (bins) and counts how many fall into
each. Great for frequency analysis.

Waterfall Running totals Visualizes running additions and subtractions. Often used for cash
Chart flow, profit/loss.

Treemap Hierarchical data Uses nested rectangles to show proportions within categories.
Sunburst Multi-level Circular version of a treemap. Shows relationships across levels.
Chart hierarchies

📌 When to Use Which?

 Comparing categories → Column, Bar

 Showing change over time → Line, Area
 Showing percentages → Pie, Doughnut
 Exploring relationships → Scatter, Bubble
 Analyzing distribution → Histogram
 Layered hierarchy → Treemap, Sunburst
 Mixed data types → Combo Chart
 Showing step-by-step changes → Waterfall

🎨 Visual Example (Conceptual)

Goal Best Chart

Monthly sales trend Line Chart

Top 5 product performance Column Chart

 Expense breakdown Pie Chart

Sales vs. cost correlation Scatter Plot

Department budget allocation Treemap

Net profit flow Waterfall Chart

 XY Scatter chart:-

An XY Scatter Chart (also called a Scatter Plot) in Excel is used to visualize the relationship
between two numeric variables. It places one variable on the X-axis and the other on the Y-
axis, and plots points where the two values intersect.

📈 When to Use an XY Scatter Chart

 To show correlation between two variables (e.g., height vs. weight)

 To identify trends, clusters, or outliers
 To analyze patterns in scientific, engineering, or financial data.

🧪 Example Data for Scatter Chart

Hours Studied Exam Score
1 50
2 55
3 60
4 65
5 72
6 80

✅ How to Create an XY Scatter Chart in Excel

Manually:
 1. Select the two columns of data.
2. Go to Insert > Scatter Chart > Choose the first option (plain markers).
3. Excel creates a chart with:
o X-axis: Hours Studied
o Y-axis: Exam Score

With Trendline (Optional):

 Click on a data point.

 Right-click > Add Trendline
 Choose Linear, Exponential, etc.
 Optionally, check “Display Equation on chart” to show the regression line.

⚙️Creating an XY Scatter Chart Using VBA

Here's a basic macro to insert a scatter plot:

vba
CopyEdit
Sub CreateXYScatterChart()
Dim ws As Worksheet
Dim chartObj As ChartObject
Dim chartRange As Range

Set ws = ActiveSheet
Set chartRange = [Link]("A1:B7") ' Adjust to your data range

Set chartObj = [Link](Left:=300, Width:=400, Top:=50,

Height:=300)
With [Link]
.ChartType = xlXYScatter
.SetSourceData Source:=chartRange
.HasTitle = True
.[Link] = "Study Hours vs Exam Score"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).[Link] = "Hours Studied"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).[Link] = "Exam Score"
End With
End Sub

🔍 Key Features of Scatter Charts

Feature Description
X-Y control You can define exact X and Y values
 Multiple series Plot multiple data sets in one chart
Trendlines Useful for linear regression & forecasting
Marker styles Customize colors, sizes, and shapes

 Area chart:-

An Area Chart in Excel is used to display quantitative data over time or across categories,
with the area beneath the lines filled in. It’s ideal for showing cumulative totals, stacked data,
and the relative contribution of parts to a whole over time.

📊 What Is an Area Chart?

 Combines a line chart with a filled area below the line.

 Helps emphasize the volume of data.
 Can be stacked or 100% stacked to show contributions.

🧰 When to Use an Area Chart

✅ Good for:

 Visualizing trends over time

 Highlighting total values and how they’re built from components
 Showing part-to-whole relationships when using stacked versions

🚫 Not good for:

 Comparing precise values between series (lines may overlap and confuse)
 Non-time-based categories (e.g., brands, departments)

🧪 Example Data
Month Product A Product B
Jan 30 20
Feb 40 25
Mar 50 30
✅ How to Create an Area Chart in Excel

Manually:

1. Select your data (including labels).

2. Go to Insert > Area Chart > Choose:
o Area – for single series
o Stacked Area – for showing how components build up to a total
o 100% Stacked Area – to show proportional contributions over time

Formatting:

 Use light colors to avoid overwhelming visuals.

 Add data labels or hover tooltips for clarity.
 Consider adding a line chart overlay for better precision.

Creating an Area Chart with VBA

Here’s a simple macro to create a basic stacked area chart:

vba
CopyEdit
Sub CreateAreaChart()
Dim ws As Worksheet
Dim chartObj As ChartObject
Dim chartRange As Range

Set ws = ActiveSheet
Set chartRange = [Link]("A1:C4") ' Adjust range to fit your data

Set chartObj = [Link](Left:=300, Width:=400, Top:=50,

Height:=300)
With [Link]
.ChartType = xlAreaStacked
.SetSourceData Source:=chartRange
.HasTitle = True
.[Link] = "Product Sales Over Time"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).[Link] = "Month"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).[Link] = "Sales Units"
End With
End Sub

Types of Area Charts

Type Use Case
 Area (standard) Single series over time
Stacked Area Multiple series showing buildup to total
100% Stacked Area Emphasize % contribution over time
3D Area Adds depth (less used for clarity)

Tips for Effective Area Charts

 Don’t overuse stacked areas — they can get cluttered.

 Use transparent fills to avoid hiding overlapping areas.
 Keep the Y-axis zero-based to preserve data integrity.
 Label your axes and use a legend when charting multiple series.

 Surface chart:-

A Surface Chart in Excel is a 3D chart type used to show data patterns across two variables,
much like a topographic map. It’s ideal for visualizing combinations of values (X and Y) and
their resulting Z values using color and elevation.

🌄 What Is a Surface Chart?

 Plots three-dimensional data using X, Y, and Z values.

 X and Y axes are horizontal (like rows and columns).
 Z-axis (vertical) represents values with colors or height.
 Useful for finding optimal combinations of two inputs.

🧪 Example Data Layout

To use a surface chart, data must be laid out like a matrix:

A B C D

10 1.5 2.0 2.5 3.0

20 2.1 2.4 2.8 3.3

30 2.7 3.1 3.6 4.0

40 3.0 3.5 4.1 4.6
  Top row: X-axis labels
 First column: Y-axis labels
 Data: Z-values (height or color)

✅ How to Insert a Surface Chart in Excel

Step-by-Step:

1. Arrange your data in a grid/matrix format as shown.

2. Select the entire range (including headers).
3. Go to Insert > Charts > Surface.
4. Choose from:
o 3-D Surface
o Wireframe 3-D Surface
o Contour (top-down view with colored bands)
o Wireframe Contour

🎨 Chart Types Explained

Chart Type Description

3-D Surface Full 3D view with colors representing elevation

Wireframe 3-D Mesh without color fill (harder to read but cleaner)

Contour Like a heat map with top-down view of color bands

Wireframe Contour Only the contour lines, no color

Create a Surface Chart with VBA

You can also generate a surface chart using a macro:

vba
CopyEdit
Sub CreateSurfaceChart()
Dim ws As Worksheet
Dim chartObj As ChartObject
Dim dataRange As Range

Set ws = ActiveSheet
Set dataRange = [Link]("A1:E5") ' Adjust for your data layout
 Set chartObj = [Link](Left:=300, Width:=500, Top:=50,
Height:=350)
With [Link]
.SetSourceData Source:=dataRange
.ChartType = xlSurface
.HasTitle = True
.[Link] = "3D Surface Chart"
End With
End Sub

🎯 When to Use a Surface Chart

✅ Use when:

 You have three variables and want to understand how they relate.
 You're analyzing optimal combinations (e.g., pricing vs. volume vs. profit).
 You want to show gradients or surface shapes in engineering, finance, or science.

🚫 Avoid when:

 Your data isn't in a complete matrix/grid format.

 You need precise comparison between values (not ideal for that).

📌 Tips for Better Surface Charts

 Use lighter colors or custom color scales for better readability.

 Rotate the 3D chart for the best angle.
 Use Contour version for cleaner visual summaries.
 Surface charts work best with numeric axes only (no categories).

 Bubble chart:-

A Bubble Chart in Excel is a type of XY scatter plot that adds a third dimension by using the
size of the bubbles to represent a third variable. It’s ideal for visualizing relationships between
three numeric variables at once.

🔵 What Is a Bubble Chart?

 X-axis → First variable

 Y-axis → Second variable
 Bubble size → Third variable (e.g., magnitude, volume, cost)
Each bubble’s position shows two values, and its size conveys a third.

📊 Example Use Case

Product Sales (X) Profit (Y) Market Share (%)

A 120 45 15

B 200 80 25

C 150 60 20

 X-axis: Sales
 Y-axis: Profit
 Bubble size: Market Shapes.

✅ How to Create a Bubble Chart in Excel

Step-by-Step:

1. Arrange your data in three columns: X, Y, and Bubble Size (Z).

2. Select the data (without product names).
3. Go to Insert > Chart > Bubble.
4. Choose:
o Bubble
o 3-D Bubble (adds 3D effect, not an extra variable)

Optional:

 Add data labels manually or with VBA for names like Product A, B, etc.
 Use the Format Data Series panel to adjust transparency, color, or size scaling.

🎨 Example Data Layout (Excel Ready)

X (Sales) Y (Profit) Z (Market Share)

120 45 15

200 80 25
 150 60 20

⚙️Create a Bubble Chart Using VBA

Here’s a macro to generate a bubble chart:

vba
CopyEdit
Sub CreateBubbleChart()
Dim ws As Worksheet
Dim chartObj As ChartObject
Dim chartRange As Range

Set ws = ActiveSheet
Set chartRange = [Link]("A1:C4") ' Adjust for your data

Set chartObj = [Link](Left:=300, Width:=500, Top:=50,

Height:=350)
With [Link]
.ChartType = xlBubble
.SetSourceData Source:=chartRange
.HasTitle = True
.[Link] = "Sales vs Profit (Bubble = Market Share)"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).[Link] = "Sales"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).[Link] = "Profit"
End With
End Sub

📌 Best Practices for Bubble Charts

Tip Why It Matters

Use transparency Helps when bubbles overlap

Label bubbles clearly Adds context for each data point

Keep bubbles proportional Avoid misleading scale

Don't overuse Best for small-to-medium data sets (5–20)

🔍 Bubble Chart vs. Scatter Plot

Feature Scatter Plot Bubble Chart
Dimensions 2 3

Data Points X, Y X, Y, Bubble Size (Z)

Best For Correlations Multi-variable analysis