A

IT SKILLS LAB

On

“PIVOT TABLE”
-:SUBMITTED TO:-

Faculty of Management Studies & Commerce (MBA), HRIT University,

Ghaziabad for the partial fulfillment of the award of the Degree of Master of
Business Administration

HRIT UNIVERSITY, GHAZIABAD

(2024-26)

Under the guidance of: SUBMITTED BY:

Ms. Jhalak Saxena Monu Pal
Assistant Professor MBA – 2nd Semester
HRIT University, Ghaziabad 240709033

1
 HRIT UNIVERSITY
Established under the provision of the Uttar Pradesh Private University Act
2019
Governed by Harish Chandra Ramkali Charitable Trust Ghaziabad
(ISO 9001:2008 & ISO 14001:2004 Certified)

CERTIFICATE
Date:

This is to certify that Mr. MONU PAL is a Bonafide student of MBA-2ND Semester batch
(2024-26) of HRIT University, Ghaziabad and has undertaken IT LAB SKILLS-2 titled
‘PIVOT TABLE’ during the study of this semester.
I wish him all the best for his/her future endeavors.

Ms. Jhalak Saxena Ms. Rashmi Tyagi

(Assistant Professor) HOD-MBA

2
 Student’s Undertakings

I Monu Pal S/o Mr. Suresh Pal am pursuing Master of Business Administration (MBA) 2nd
Semester from HRIT University, Ghaziabad in the session 2024-26.
I hereby declare that this IT LAB SKILLS -2 report titled “PIVOT TABLE” is the outcome of
my own effort under the guidance of Ms. Jhalak Saxena designation Assistant Professor The
same report has not been submitted earlier to any Institute/ University for awarding any degree/
diploma of MBA or any other professional course.

Date: ………………… Monu Pal

Place: HRIT University, Ghaziabad Roll. No: 240709033

3
 ACKNOWLEDGEMENT

IT LAB SKILLS -2 is a bridge connecting the educational qualification and professional use. It is

the path leading to success by shouldering responsibilities under the careful guidance of seniors

and experienced personnel without fear and failure.

It gives me immense pleasure to take the opportunity to remember and thanks the personalities

who have involved with this project work. I express my sincere thanks and deep gratitude who are

directly and indirectly associated in completion of this project.

I would like to thanks to Ms. Jhalak Saxena designation Assistant Professor for assigning an

extremely challenging project thereby giving unique opportunity to meaningful contribution of

such growing and vibrant organization, guiding throughout the project, without his/her help the

project would have not added enough value. I am extremely grateful for the time he/she spent from

his/her busy schedule.

MonuPal

4
 INDEX
SIGNATURE
S.N0 PARTICULAR
NO.

1 HOW TO CREATE A PIVOT TABLE 3-8

HOW TO ANALYZING DATA USING GOAL SEEK AND
2 SLOVER IN PIVOT TABLE
9-10
HOW TO CREATE A SUMMERY REPORT IN
3 PIVOT TABLE 11-1
HOW TO VALIDATE AND AUDIT DATA IN
4 PIVOT TABLE 14-23
HOW TO SET, EDIT VALIDATE CRITERIA FOR
DATA ENTRY IN A CELL RANGE LIKE: WHOLE
5 NUMBER, DECIMAL, DATE, TIME 24-27
HOW TO CREAT APPLICATION IN
28-41
6 SPREADSHEET AND MACROS
7 HOWTO CREATE A COLUMN CHART 42-43

8 HOW TO CREATE A BAR CHART

44-50

9 HOW TO CREATE A LINE CHART 51-54

10 HOW TO CREATE A PIE CHART 55-57

HOW TO CREATE A COMBINED CHART LIKE:
11 COLUMN AND LINE, COLUMN AND AREA 58-63

12 HOW TO DISPLAY AN IMAGE IN CHART ARE 64-66

5
 TO CREATE A PIVOT TABLE

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the
significance from a large, detailed data set.
Our data set consists of 21 3 records and 6 fields. Order ID, Product, Category, Amount,
Date and Country.

Insert a Pivot Table

To insert a pivot table, execute the following steps.
1. Click any single cell inside the data set.
2. On the Insert tab, in the Tables group, click PivotTable.

The following dialog box appears. Excel automatically selects the data for you. The default
location for a new pivot table is New Worksheet.
3. Click OK.

CEO'S'

6
 rag le s
The PivotTable Fields pane appears. To get the total amount exported of each product, drag the
following fields to the different areas.
1. Product field to the Rows area.
2. Amount field to the Values area.
3. Country field to the Filters area.

Below you can find the pivot table. Bananas are our main export product. That's how easy pivot
tables can be!

7
Sort
To get Banana at the top of the list, sort the pivot table.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Sort, Sort Largest to Smallest.

Result.

1 Country Sum
2
Row Labels
3 of Amount
•l
4 Banana 340295
Apple
5 Broccoli
carrots 191257
6 Orange 142439
7 Beans 136945
S Mango 104438
9 Grand Total 57281
10 57079
11 1029734
12
Filter
Because we added the Country field to the Filters area, we can filter this pivot table by
Country. For example, which products do we export the most to France?
1. Click the filter drop-down and select France.
Result. Apples are our main export product to France.

Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts
of specific products.
Change Summary Calculation
By default, Excel summarizes your data by either summing or counting the items. To change
the type of calculation that you want to use, execute the following steps.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Value Field Settings.

Cell'...
Number Fc rmat.
Eefresh

Sort
X Remor •Sum Amount"

Values Ey

3. Choose the type of calculation you want to use. For example, click Count.
 6

4. Click 0K.
Result. 16 out of the 28 orders to France were 'Apple' orders.

Two-dimensional Pivot Table

If you drag a field to the Rows area and Columns area, you can create a two-dimensional
pivot table. First. insert a pivot table. Next, to get the total amount exported to each
country, of each product, drag the following fields to the different areas. . Country field
to the Rows area.

2. Product field to the Columns area.

3. Amount field to the Values area.
4. Category field to the Filters area.
Below you can find the two-dimensional pivot table.

To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one
step too far for you at this stage, but it shows you one of the many other powerful pivot
table features Excel has to offer.
HOW TO ANALYZING DATA USING GOAL SEEK AND SOLVER
IN PIVOT TABLE

The Goal Seek Excel function (often referred to as What-if-Analysis) is a method of solving
for a desired output by changing an assumption that drives it. The function essentially uses
a trial and error approach to back-solving the problem by plugging in guesses until it arrives
at the answer.

For example. if the formula for revenue is equal to the number of units sold multiplied by
the selling price, Goal Seek can determine how many units have to be sold to reach $1
million of revenue, if the selling price is known. The function is extremely useful for
performing sensitivity analysis in financial modeling.

Simple Goal Seek Example

Let's look at a simple exercise first, to see how Goal Seek works. Suppose we have a very
basic model that takes the number of units sold, the retail price, and a discount to calculate
total net revenue.

The current model contains the following information:

• # of units: SOO
• Retail price: $25.00
• Selling discount: 10%
• Revenue: $1 1,250

12
Now suppose we want to find out how many units have to be sold to reach $20,000 of
revenue. See the screenshot below and follow the steps listed to use the Goal Seek Excel
tool.

Steps to Use Goal Seek Excel:

Put the cursor on the cell that contains the output you want to change (revenue)
2. On the Data ribbon, select What-if-analysis, then select Goal Seek (keyboard shortcut
is Alt, A, W, G)
3. When the dialog box appears (as shown below) make "Set cell" equal to the revenue
cell (EIO)
4. Set "To value" equal to the output you want to achieve (type the number in)
S. Set "By changing cell" equal to the assumption you want to solve for of units, or cell
E4)
6. Press OK

Below is the output from the analysis. We can see that to achieve $20,000 of revenue, 889
units need to be sold. Notice that the solution is displayed directly in cell (E4), not in the
dialogue box.

Press OK to keep the solution running in the model (cell E4 will permanently change to 889
units), or press Cancel to return to the original assumption (500 units).

13
HOW TO CREATE A SUMMARY REPORT IN PIVOT TABLE
Microsoft says that 80% of people using Excel have never used a pivot table. As I near the
end of my series of 40 Days of Excel, an introduction to pivot tables.
Pivot tables are miraculous. You are given a workbook with thousands of rows of detailed
data. You can summarize that data in just a few clicks using a pivot table. I've written entire
books on pivot tables, so today, I want to walk you through building your first pivot table.

Say that you have 6464 rows of data with Customer, Product, Date, Quantity Revenue. You
want to find the revenue for the top S customers who bought widgets.

Select one cell in your data set

2. On the Insert tab of the Ribbon, select Pivot Table

14
 Start your pivot table

3. In the Create PivotTable dialog, choose Existing Worksheet. Always leave a blank
column between your data and the pivot table, so in this case, choose cell G2 for the
place to hold your pivot table.

15
Select where to build the pivot table

The PivotTable Fields panel appears on the right side of your screen. At the top of
the panel are a list of the fields in your data set. At the bottom are four drop zones
with confusing names.

Slicers Fields are

better across top
here

Numbers
to sum
Fields along here
left side here
Drag fields to these drop zones
 4. Drag the Customer field from the top ofthe PivotTable Fields list and drop it in the
Rows drop zone.
S. Drag the Revenue field from the top of the PivotTable Fields list and drop it in the
Values drop zone.
6. Drag the Product field to the Filters drop zone.
7. Open the filter drop-down in H2 and choose Widget. At this point, you will see a
summary of the customers who bought widgets/

Just 7 steps to build this summary report

8. There are two PivotTable tabs in the Ribbon. Go to the Design tab. Choose Report
Layout, Show in Tabular Form. This changes the heading in G3 from Row Labels to
Customer. It also makes the pivot table better if you add more row fields later.
9. Open the drop-down in G3. Choose Value Filters, Top Ten. In the Top 10 Filter
(Customer) dialog, choose Top, 5 Items by Sum of Revenue. Click OK.
10. Pivot tables never choose the right number format. Select all of the Revenue
cells, from H4:H9. Assign a Currency format with 2 decimal places.
1 1.1f you want the report sorted with the largest customer at the top, choose any
one revenue cell and click the Sort Descending (ZA) icon on the Data tab. This is a
shortcut for opening the drop-down in G3 and selecting Sort Z to A.

At this point, you've solved the original question:

The finished pivot table

However, Pivot Tables are easy to change. Now that you've answered the first question, try
any of these:
 Change the dropdown in HI from Widget to Gadget to see the top customers for
gadgets.
Double-click the revenue in H4 to see a list of all the records that make up that data.
The report appears on a new worksheet to the left of the current worksheet. Click
the Pivot Chart icon on the Analyze tab to chart the top 5 customers

13
HOW TO VALIDATING AND AUDITING DATA IN PIVOT

TABLE
You might want to check formulas for accuracy or find the source of an error. Excel Formula
Auditing commands provide you an easy way to find

Which cells are contributing in the calculation of a formula in the active cell.
Which formulas are referring to the active cell.
These findings are shown graphically by arrow lines that makes the visualization easy. You
can display all the formulas in the active worksheet with a single command. If your
formulas refer to cells in a different workbook, open that workbook also. Excel cannot go to
a cell in a workbook that is not open.
Setting the Display Options
You need to check whether the display options for the workbooks you are using are correctly
set.

Click FILE > Options.

In the Excel Options dialog box, click Advanced.
In Display options for the workbook — o Select the workbook.
 Check that under For objects, show, All is selected.

Repeat this step for all the workbooks you are

auditing.
Tracing Precedents

Precedent cells are those cells that are referred to by a formula in the active cell.
In the following example, the active cell is C2. In C2, you have the formula
B2 and C4 are precedent cells for C2.
 Formula Auditing group.

C4 to C2 will be displayed, tracing the

Note that for tracing precedents ofa cell, the cell should have a formula with valid
references. Otherwise, you will get an error message.
Click in a cell that does not contain a formula or click in an empty cell. Click
Trace Precedents in the Formula Auditing group.
You will get a message.
Click Remove Arrows in the Formula Auditing group.

e arrows In e wor s ee WI Isappear.

Tracing Dependents

Dependent cells contain formulas that refer to other cells. That means, if the active cell
contributes to a formula in another cell, the other cell is a dependent cell on the active cell.
In the example below, C2 has the formula =B2'C4. Therefore, C2 is a dependent cell on the
cells B2and C4

To trace the dependents of the cell 82,

Click in the cell 82.
Click the Formulas tab.
Click Trace Dependents in the Formula Auditing group.
AnarrowappearsfrorrvB2to C20showing C2-isdependent on B2.
To trace the dependents of the cell C4 —

Click in the cell C4.

Click the Formula tab > Trace Dependents in the Formula Auditing group.
Another arrow appears from C4 to C2, showing C2 is dependent on C4 also.

Click Remove in the Formula Auditing

group. All the arrows in the worksheet will disappear.
Note — For tracing dependents of a cell, the cell should be referenced by a formula in
another cell. Otherwise, you will get an error message.

Click in the cell 86 is not referenced by any formula or click in any empty cell.
Click Trace Dependents in the Formula Auditing group. You will get a message.

Working with Formulae

22
You have understood the concept of Precedents and Dependents. Now, consider a
worksheet with several formulae.

in Exam Results table.

I-under-P
Catego Click Trace Precedents. The cell to its left
(Marks) and the range E4:F8 will be mapped as the precedents.
Repeat for all the cells under Pass Category in Exam Results table.

ell-under-Pass-Category in
Student Grades table.
Click Trace Dependents. All the cells under Pass Category in Exam Results table
will be mapped as the dependents.

Showing Formulas

23
The worksheet below contains the summary of sales by the salespersons in the regions East,
North. South. and West.

Name Total Sales

1326
Mathew 1625
Katherine 1957
Jane 1687 tab on the Ribbon.
5323 Click Show Formulas in the Formula Auditing group. The
Total
Formulas in the worksheet will appear. so that you will
know which cells contain formulas and what the formulas
are.

Click Trace Precedents. A worksheet icon appears at the end of the arrow. The
worksheet icon indicates that the precedents are in a different worksheet.

Double-click on the arrow. A Go TO dialog box appears, showing the precedents.

24
As you observe, there are four precedents, on four different worksheets.
Click a reference of one of the
The reference appears in the Reference box.
Click OK. The worksheet containing that precedent appears.

Evaluating a Formula
To find how a complex formula in a cell works step by step, you can use Evaluate Formula
command.
Consider the formula NPV (Middle Year) in the cell C14. The formula is
=SQRT (1 +
Click in the cell Cl 4.
Click the FORMULAS tab on the Ribbon.
Click Evaluate Formula in the Formula Auditing group. The Evaluate Formula
dialog box appears.

25
 is displayed in the box under Evaluation. By
In the F«mula dialog box, the formula clicking the Evaluate button several times,
the formula gets evaluated step-wise. The expression with an underline will always be
executed next.

Here, C2 is underlined in the formula. So, it is evaluated in the next step. Click Evaluate.

uated as 0.2. 1+02 is underlined showing it

26
 as the next step. Click Evaluate.

is underlined showing it as next step.

SQRT(I .2) will be evaluated as 109544511501033. CIO is underlined showing it as next step.
Click Evaluate.

CIO will be evaluated as 4976.8S18S18S1 S.

1.0954451 1501033'4976.8518518515 is underlined showing it as next step.
Click Evaluate.

27
 103 •49768518518SiS will
be evaluated as 5,451.87.
There are no more expressions to evaluate and this is the answer. The Qaluate button will be
changed to button, indicating completion of evaluation.

Error Checking

It is a good practice to do an error check once your worksheet and/or workbook is ready with
calculations.
Consider the following simple calculations.

The calculation in the cell has resulted in the error #DIV/O!.

Click in the cell CS.
Click the FORMULAS tab on the Ribbon.
Click the arrow next to Error Checking in the Formula Auditing group.
In the drop-down list, you will find that is deactivated,
indicating that your worksheet has no circular references.

Select Trace Error

from the drop-down list.
The cells needed to compute the active cell are indicated by blue arrows.

28
 —Click-Remove-Arrows:—
Click the arrow next to Error Checking.
Select Error Checking from the drop-down list.

ec Ing og ox appears.

Observe the following —

If you click Help on this error, Excel help on the error will be displayed.
If you click Show Calculation Steps, Evaluate Formula dialog box
appears. If you click Error, the Error Checking dialog box closes and if you click
command again, it ignores this error.
If you click you will be taken to the formula in the formula bar, so that you
can edit the formula in the cell.

HOW TO SET, EDIT VALIDATION CRITERIA FOR DATA ENTRY IN

A CELL RANGE LIKE: WHOLE NUMBER, DECIMAL, DATA, TIME
Create a Validation Rule
29
 1. Select the cells you want to validate.

2. Click the tab.

3. Click the button.

4.
5. Select the type of data you want to allow.

No validation criteria applied.

Allows a whole number between the
minimum
and maximum limits set.
DedmaE Allows a decimal or a percent entered as a decimal
between the set limits.
Allows a value from a list of choices. A list arrow appears in
the cell, and users can choose from the list.
Allows a date within set limits.
Time Allows a time within set limits.
Tat Allows text containing a certain number of characters.
Allows a formula to be entered to calculate what is
allowed in the cell.

30
 6. Specify the data validation rules.

The validation options will vary depending on the option selected in the
Allow field.

7. Click OK

The data validation is set for the selected cell(s). When a user tries to enter
data that is not valid, Excel will prevent the entry and display a message
about the cell being restricted.
To find validated data in a worksheet, click the Find & Select button in the
Editing group on the Home tab and select Data Validation. The validated
cells are highlighted.
Add Input and Error Messages

31
Prevent data validation issues by setting up Excel to display a message whenever a cell or
range Of cells is selected. These messages are useful when other people will be entering
data in your worksheet. An error message can be configured to appear when data is entered
that does not match a data validation rule.

. Select the cells where you want an input message to appear.

2. Click tab.
3. Click the button.
4. Click the —e tab.
5. Enter an input message.

6. Click the Nat tab.

7. Select an error alert style.

32
 Prevents users from adding
invalid data in a cell.
Warns that the data
entered is invalid, but users
can click Ye to accept the
invalid entry, No to edit it, or
Cancel to remove it.
Informs users
that the data entered is
invalid, but users can click OK
to accept the invalid entry or
to remove it.
8. Enter an error alert
message.

9. Click OK
10. Select a cell with an input message.

Now when a cell in the range is selected, the title and message display. If you enter an invalid
value, a custom error message appears.

HOW TO CREATE APPLICATION IN SPREADSHEET AND

MACROS

What Are Excel Macros?

33
Macros are code that automate work in a program—they let you add your own tiny
features and enhancements to help you accomplish exactly what you need to do, quickly
with just a click of abutton. In a spreadsheet tool like Excel, macros can be especially
powerful. Hidden behind the normal user interface, they are more powerful than standard
functions you enter into a cell (e.g.—IF (A2<100, 100, A2)).

These macros make Excel work for you. They replace actions that you do manually—
everything from formatting cells, copying values, and calculating totals. So with a few clicks
you can quickly replace repetitive tasks.

To make these macros, you can simply record your actions in Excel to save them as
repeatable steps or you can use Visual Basic for Applications (VBA), a simple programming
language that's built into Microsoft Office. We'll show you how to use both below, as well as
share examples of Excel macros to help you get started.

Tip: This guide and all examples are written in Qcel 2016 for Windows, but the
principles apply to Excel 2007 and newer for both Mac and PC.

Why Use Excel Macros?

Learning how to automate Excel is one of the easiest ways to speed up your work—
especially because Excel is used in so many work processes. Say every week you export
analytics data from your content management system (CMS) to create a report about your
site. The only problem is, those data exports aren't always in an Excel-friendly format.
Thefre messy and often include far more data than your report requires. This means you
have to clean up empty rows, copy and paste data into the right place, and create your
own charts to visualize data and make it print-friendly. All of these steps may take you
hours to complete.
If there only was a way to press one button and let Excel do it for you in an instant... well,
can you guess what I'm about to say next?

There is!

All it requires is a little bit of time to set up a macro, and then that code can do the work for
you automatically every time. It's not even as difficult as it sounds.

34
How to Build Your First Excel Macro

You already know your way around Excel, and are familiar with its grid of cells where you
enter your text and functions. To build Excel macros, though, you'll need an extra tool
that's built into Excel: the Visual Basic Editor.

Before You Proceed: Be sure to download our project file — you'll need it for later to follow
our Excel macros tutorial.

Meet The VBA Editor

Excel has a built-in tool for writing macros called the Visual Basic Editor—or VBA Editor for
short. To open that, open a spreadsheet and use the shortcut Alt + Fl 1 (for
Mac: Fn + Shift + Fl 1).

The new window that pops up is called the VBA Editor. It's where you'll edit and store all of
your macros. Its layout may look a bit different from this screenshot, but you can move the
windows around to the order you want. Just be sure to keep the Project Explorer pane open
so you can easily edit your macros.

Your macros will be made up of "Modules," or files with your VBA code. You'll add a new
module or open an existing one in the VBA Editor, then type in the code you want. To insert
a module, click "Insert" and then click "Module". You'll then see the blank space to write
your code on the right.

35
How to an Excel Macro

There are two ways to make a macro: code it or record it. The main focus of this article is on
the former, but recording a macro is so simple and handy, it's worth exploring too. Recording
a macro is a good way of getting to know the basics of VBA. Later on, it serves as handy
storage for code that you don't need to memorize.

When you record a macro, you tell Excel to start the recording. Then you perform the tasks
you want to be translated into VBA code. When you're done, tell Excel to stop recording
and you can use this new macro to repeat the actions you just performed again and again.

There are limitations to this, so you can't automate every task or become an expert in
automation by only recording. You'll still need to type or edit code manually sometimes. But
it's still a handy way to get started. Here's how: 1 . Go to the "VieW' tab of the ribbon and
click the tiny arrow below the "Macros" button. 2. Then click "Record Macro" 3. Type in the
name of your macro and click "OK" to start the recording. 4. Perform the actions in your
spreadsheet you want to be turned into a macro. 5. When you're done, go to the "View" tab,
click the tiny arrow below the "Record Macro" button again and select "Stop recording".

36
Now, use the shortcut Alt + Fl 1 (for Mac: Fn + Shift + Fl 1) to open the VBA Editor, and double-
click "Module 1" in the Project Explorer.

This is your first code! Amazing, right? You may not have written it yourself, but it's still
generated from your actions.

Yours probably look different than mine. Can you guess what my code does?
Sub Makebold is just the text Sub followed by the name I entered when I started
recording.

The green line doesn't actually do anything—it's a comment where you could add an
explanation of what the macro does.

[Link] = True makes the values in the selected cells bdd

End sub simply tells Excel that the macro stops here.

Now, what will happen if I change the True part of the third line to False? The macro would
then remove any bold formatting from the selection instead of making it bold.

That's how you record a simple macro. But the real power of macros comes when you can
write it yourself—so let's get started learning to write simple VBA code.

How to Code Your Oun acel Macros

37
Macros are just bits of code in Excel that do your bidding. Once you write the code in the
VBA Editor, you can run it and let the code work its magic on your spreadsheet. But what's
even better is to build your macro into your spreadsheet, and the best tool for that is buttons.

So first, before we start coding, let's add a button to run our macro.

Add a Button to Run Your Macro

You can use various Excel objects as buttons for running macros, but I prefer to use a
shape from the "Insert" tab. When you have inserted your shape, right click it and select
"Assign Macro..." Then select the macro you want to run when the shape is clicked—
perhaps the one you just made with a recording and save it by clicking "OK".

Now, when you click the shape which we just turned into a button, Excel will run the macro
without having to open the code each time.

There's one other thing to note before we get started: saving your spreadsheet with
Macros. By default, Excel spreadsheet files with an .xlsx extension cannot include macros.
Instead, when you save your spreadsheet, select the "Excel Macro-Enabled Workbook
(*.xlsm)" format, and add your file name as normal.

Go ahead and do that to save your spreadsheet before we start coding.

Now, let's get started with actual coding!

38
Copying and pasting is the simplest way to move data around, but it's still tedious. What if
your spreadsheet could do that for you? With a macro, it could. Let's see how to code a
macro that will copy data and move it around in a spreadsheet.

Open the project file you downloaded earlier and make sure the "Copy, cut, and paste"

sheet is selected. This is a sample employee database with the names, departments, and
salaries of some employees.
Let's try to copy all the data in columns A through C into D through F using VBA. First, let's
look at the code we need:

Copying Cells wit' VBA

Copying in VBA is quite easy. Just insert this code into the VBA Editor. Range("lnsert range
here").Copy. Here's some examples:

4— copies column A through C

Range("A1 :CI 00").Copy 4— copies the range Al 00

Remember when you recorded a macro before? The macro I-ed Sub
NameofmacroO and End sub at the top and bottom line of the code. These lines must
always be included. Excel makes that easy, too: When you type in "Sub" followed by the
macro name in the beginning of the code, the End sub is automatically inserted at the
bottom line.

Tip: Remember to enter these lines manually when you're not using the macro recorder.

Pasting Cells with VBA

Pasting can be done in different ways depending on what you want to paste. 99% of the time,
you'll need one of these two lines of code:

Range("The cell/area where you want to paste").Pastespecial 4— pastes as normal

(formulas and formatting)

39
 Range("The cell/area where you want to paste").Pastespecial xlPasteValues 4— only
pastes values

Cutting Cells with VBA

If you want to relocate your data instead of copying it, you need to cut it. Cutting is quite
easy and follows the exact same logic as copying.

Here's the code: Range("lnsert range here").Cut

When cutting, you can't use the 'PasteSpecial' command. That means that you can't paste
values only, or formatting only. Therefore, you need these lines to paste your cells with
VBA: Range("lnsert where you want to paste").Select [Link]

For example, here's the code you'd need to cut the range A:C and paste it into DI:

[Link]

Copying, cutting, and pasting are simple actions that can be done manually without
breaking a sweat. But when you copy and paste the same cells several times a day, a
button that does it for you can save a bunch of time. Additionally, you can combine
copying and pasting in VBA with some other cool code to do even more in your
spreadsheet automatically.

Adding Loops to VBA

I just shoned you how to take a simple action (copying and pasting) and attach it to a
button, so you can do it with a mouse click. That's just one automated action. When you
have the code to repeat itself, though, it can do longer and more complex automation
tasks in seconds.

40
Take a look at the "Loops" sheet in the project file. It's the same data as in the previous
sheet, but every third row of the data is now moved one column to the right. This type of
faulty data structure is not unusual when exporting data from older programs.
This can take a lot of time to fix manually, especially if the spreadsheet includes thousands
of rows instead of the small sample data in this project file.

Let's make a loop that fixes it for you. Enter this code in a module, then look at the
explanations below the picture:

o
Ena Sue

. This line makes sure the loop starts at the top-left cell in the sheet and not accidentally
messes the data up by starting somewhere else.

2. The For i = 1 To 500 line means that the number of times the loop has run
(represented by i) is an increasing number that starts with 1 and ends with 500. This
means that the loop will run 500 times. The number Of times the loop should run
depends on the actions you want it to do. Use your good sense here. SOO times is
way too many for our sample dataset. but would fit perfectly if the database had 1500
rows of data.

3. This line recognizes the active cell and tells Excel to move 3 rows down and select
that cell, which then becomes the new active cell. If it was every fourth row that was
misplaced in our data, instead of every third, we could just replace the 3 with a 4 in
this line.

4. This line tells Excel what to do with this newly selected cell. In this case, we want to
delete the cell in such manner that the cells to the right of the cell are moved left. That
is achieved with this line. If we wanted to do something else with the misplaced rows.
this is the place to do it. If We wanted to delete every third row entirely, then the line
should've been: [Link].
41
 S. This line tells Excel that there are no more actions within the loop. In this case, 2 and
S are the frame of the loop and 3 and 4 is the actions within the loop.

When we run this macro, it will result in a neat dataset without any misplaced rows.

Adding Logic to VBA

Logic is what brings a piece of code to life by making it more than just a machine that can
do simple actions and repeat itself. Logic is what makes an Excel-sheet almost human—it
lets it make intelligent decisions on its own. Let's use that to automate things!

This section is about IF-statements which enables the "if-this-then-that" logic, just like the
IF-function in Excel.

Let's say the export from our website CMS was even more erroneous than expected. Every
third row is still misplaced, but now, some of the misplaced rows are placed 2 columns to
the right instead of 1 column to the right. Take a look at the sheet "IF-statement" in the
project file to see what it looks like.

How do we take this into account in our macro? We add an IF-statement to the loop!

Let's formulate what we want Excel to do:

We start in cell A 1. Then we go three rovvs down (to ce//A4, A7, A 10, etc.) until there's no
more data. Every time we go three rows down we check this row to see if the data has been
misplaced by 1 or 2 columns. Then move the data in the row either 1 or 2 columns to the
left.

Now, let's translate this into VBA code. We'll start with a simple loop, as before:

42
The only thing we need now is to write what should happen within the loop. This is the "go
three rows down" part that we developed back in the section about loops. Now we're
adding an IF-statement that checks hav much the data is misplaced and corrects it
correspondingly.

This is the final code to copy into your module editor, with each step explained below:

. This is the first part of the IF-statement. It says that ifthe cell right of the active cell (or
[Link](O,1) in VBA code) is blank (represented by = thendo something. This
something is the exact same action as we did when we created the loop in the first
place: deleting the active cell, and moving the active row one cell to the left
(accomplished with the [Link] Shift:=xlToLeft code). This time, we do it two
times instead of one, because there are two blank cells in the left side of the row.
2. If the above is not true, and the cell right of the active cell is not blank, then the
active cell is blank. Therefore, we only need to delete the active cell and move the
active row one cell to the left one time.

The IF-statement must always end with an End If to tell Excel it's finished running. After the
IF-statement, the loop can run again and again, repeating the IF-statement each time
43
Congratulations, you've just created a macro that can clean up messy data! See the
animation below to see it in action (If you haven't already tried it yourselO.

Automate Excel Without

Excel macros have only one problem: they're tied to your computer, and they can't run in
the Excel Web App or on your mobile device. And they're best at working on data already
in yöüüSPFéääSfi€et, making it difficult to get new data from your other apps into your
spreadsheet.

App integration tool Zapier can help. It connects the Office 365 for Business edition of Excel
to hundreds oGfhéF apps—Stripe, Salesforce, Slack, and more—so you can log data to your
spreadsheet automatically or start tasks in other apps right from Excel.

Here's how it works. Say you want to save your Typeform form entries to an Excel
spreadsheet. Just create a Zapier account, and click the Make a Zap button in the top right
corner. Then, select Typeformin the app picker, and set it to watch your form for new entries.

Zapier can watch your Typeform form for new entries

Test your Zap, then click Continue to add another step to your Zap. This time we'll select
the Excel app, and choose to Add a Rowto our spreadsheet. You could also update a row,
or search your spreadsheet for a specific row if you wanted.

44
Zapier lets you add, update, or find rows in your Excel spreadsheet

Now, choose your spreadsheet and worksheet, then click the + icon on the right of each
spreadsheet row to select the correct form field to save to that spreadsheet row. Save and
test your Zapier integration, then turn it on. Then every time your Typeform form gets filled
out, Zapier will save that data to your Excel spreadsheet.

Zapier can add your form data directly to the spreadsheet row you want

Here are some great ways to get started automating Excel with Zapier in a few clicks—or
build your own Excel integrations to connect your spreadsheets to your favorite apps.

45
 HOW TO CREATE A COLUMN CHART
Column charts are useful for showing data changes over a period of time or for illustrating
comparisons among items. In column charts, categories are typically organized along the
horizontal axis and values along the vertical axis.

EAST ASIA SALES

For information on column charts, and when they should be tsed, see Available chart types
in Office.

ExcelOutlook

To create a column chart, follow these steps:

1. Enter data in a spreadsheet.
2. Select the data.
3. Depending on the acel version you're using, select one of the following options:
Excel 201 6: Click Insert > Insert Column or Bar Chart icon, and select a column
chart option of your choice.

46
 Excel 201 3: Click Insert > Insert Column Ctwt icon, and select a column chart
option of your choice.

Excel 2010 and Excel 2007: Click Insert > Column and
select a column chart option of your choice.

You can optionally format the chart a little further. See the list below for a few options:

To apply a different chart style, click DSign > Gart Stylæ, and pick a style. To
apply a different shape style, click Format > Shape SWIs, and pick a style.
To apply different shape effects, click Format > and pick an
option such as Bevel or Glow, and then a sub option.
To apply a theme, click Page Layout > Them— and select a theme. To apply a
formatting option to a specific component a chart (such as Vertical (Value) Axis
Horizontal (Category) Axi9 Chart Area, to name a few), click Format > pick a
component in the Chart Elements dropdown box, click Format Selection, and
make any necessary changes. Repeat the step for each component you want
to modify.

47
 HOW TO CREATE A BAR CHART

A bar chart (or a bar graph) is one of the easiest ways to present your data in Excel, where
horizontal bars are used to compare data values. Here's how to make and format bar charts
in Microsoft Excel.
Inserting Bar Charts in Microsoft ace!
While you can potentially turn any set of Excel data into a bar chart, It makes more sense to
do this with data when straight comparisons are possible, such as comparing the sales data
for a number of products. You can also create combo charts in Excel, where bar charts can
be combined with other chart types to show-Oæo-types-of-data-together.
RELATED: How to Create a Combo Chart in Excel
We'll be using fictional sales data as our example data set to help you visualize how this
data could be converted into a bar chart in Excel. For more complex comparisons,
alternative chart types like histograms might be better options.
To insert a bar chart in Microsoft Excel, open your acel workbook and select your data.
You can do this manually using your mouse, or you can select a cell in your range and
press Ctrl-FA to select the data automatically.

Once your data is selected, click Insert > Insert Column or Bar Chart.
Various column charts are available, but to insert a standard bar chart, click the "Clustered
Chart" option. This chart is the first icon listed under the "2-D Column" section.

48
 ce WII automatica y ta e t e ata rom
your ata set to create the chart on the same worksheet, using your column labels to set
axis and chart titles. You can move or resize the chart to another position on the same
worksheet, or cut or copy the chart to another worksheet or workbook file.
For our example, the sales data has been converted into a bar chart showing a comparison
of the number of sales for each electronic product.

For this set of data, mice were bought the least with 9 sales, while headphones were
bought the most with 55 sales. This comparison is visually obvious from the chart as
presented.
Formatting Bar Charts in Microsoft Excel
By default, a bar chart in Excel is created using a set style, with a title for the chart
extrapolated from one of the column labels (if available).
You can make many formatting changes to your chart, should you wish to. You can change
the color and style of your chart, change the chart title, as well as add or edit axis labels on
both sides.
It's also possible to add trendlines to your Excel chart, allowing you to see greater patterns
(trends) in your data. This would be especially important for sales data, where a trendline
could visualize decreasing or increasing number of sales over time.
Changing Chart Title Text
49
To change the title text for a bar chart. double-click the title text box above the chart itself.
You'll then be able to edit or format the text as required.

you wan to remove e c art It e comp etely, select your chart and click the
"Chart
Elements" icon on the right, shown visually as a green, "+" symbol.
From here, click the checkbox next to the "Chart Title" option to deselect it.

Your chart title will be removed once the checkbox has been removed.
Adding and Editing Axis Labels
To add axis labels to your bar chart. select your chart and click the green "Chart Elements"
icon (the "+" icon).
From the "Chart Elements" menu, enable the "Axis Titles" checkbox.

Axis labels should appear for both the x axis (at the bottom) and the y axis (on the left). These
will appear as text boxes.
50
To edit the labels, double-click the text boxes next to each axis. Edit the text in each text box
accordingly, then select outside of the text box once you've finished making changes.

If you want to remove the labels, follow the same steps to remove the checkbox from the
"Chart Elements" menu by pressing the green, "+" icon. Removing the checkbox next to
the "Axis Titles" option will immediately remove the labels from view.
Changing Chart Style and Colors
Microsoft ace' offers a number of chart themes (named styles) that you can apply to your
bar chart. To apply these, select your chart and then click the "Chart Styles" icon on the
right that looks like a paint brush.

A list of style options will become visible in a drop-down menu under the '"Style" section.

Select one of these styles to change the visual appearance of your chart, including changing
the bar layout and background.

51
 ou can access e same c art styles by clicking the "'Design" tab,
under the "Chart Tools" section on the ribbon bar.
The same chart styles Will be visible under the "Chart Styles" section—clicking any of the
options shown will change your chart style in the same way as the method above.

You can also make changes to the colors used in your chart in the "Color" section of the Chart
Styles menu.
Color options are grouped, so select one of the color palette groupings to apply those colors
to your chart.

52
You can test each color style by hovering over them with your mouse first. Your chart will
change to show how the chart will look with those colors applied.
Further Bar Chart Formatting Options
You can make further formatting changes to your bar chart by right-clicking the chart and
selecting the "Forrnat Chart Area" option.

This will bring up the "Format Chart Area" menu on the right. From here, you can change
the fill, border, and other chart formatting options for your chart under the "Chart
Options" section.

You can also change how text is displayed on your chart under the "Text Options" section,
allowing you to add colors, effects, and patterns to your title and axis labels, as well as
change how your text is aligned on the chart.

53
 e further text formatting changes, you can do this using the standard text
formatting options under the "Home" tab while you're editing a label.
you want to
ma

You can also use the pop-up formatting menu that appears above the chart title or axis label
text boxes as you edit them.

so

HOW TO CREATE A LINE CHART

Line charts are used to display trends over time. Use a line chart if you have text labels,
dates or a few numeric labels on the horizontal axis. Use a scatter plot (XY chart) to show
scientific XY data.
To create a line chart. execute the following steps.
1. Select the range Al:D7.

54
2. On the Insert tab, in the Charts group, click the Line symbol.

3. Click Line with Markers.

Result:

55
Note: only if you have numeric labels, empty cell Al before you create the line chart. By
doing this, Excel does not recognize the numbers in column A as a data series and
automatically places these numbers on the horizontal (category) axis:-After-ereating
the chart, you can enter the text Year into cell Al if you like. Let's customize this line
chart.
To change the data range included in the chart, execute the following steps.
4. Select the line cynrt.
5. On the Design tab, in the Data group, click Select Data.

6. Uncheck Dolphins and Whales and click OK

Result:
To change the color of the line and the markers, execute the following steps.

7. Right click the line and click Format Data Series.

56
tn

format Oat' Series.::

The Format Data Series pane appears.

8. Click the paint bucket icon and change the line color.

9. Click Marker and change the fill color and border color of the markers.
Result:

To add a trendline, execute the following steps.

10. Select the line chart.
1 1 . Click the + button on the right side of the chart, click the arrow next to Trendline and
then click More Options.

57
The Format Trendline pane appears.

12. Choose a Trend/Regression type. Click Linear.

13. Specify the number of periods to include in the forecast. Type 2 in the Forward box.

To change the axis type to Date axis, execute the following steps.

14. Right click the horizontal axis. and then click Format Axis.

The Format Axis pane appears.

I S. Click Date axis.

Result:

Conclusion: the trendline predicts a population of approximately 250 bears in 2024.

58
 HOW TO CREATE A PIE CHART
Pie charts are used to display the contribution of each value (slice) to a total (pie). Pie
charts always use one data series.
To create a pie chart of the 2017 data series, execute the following steps.
1. Select the range Al:D2.

2. On the Insert tab, in the Charts group, click the Pie symboL

Fecommended
Chart'

3. Click Pie.

Result:

4. Click on the pie to select the whole pie. Click on a slice to drag it away from the center.
Result:

59
 Wildlife

Note: only if you have numeric labels, empty cell Al before you create the pie chart. By doing
this. Excel does not recognize the numbers in column A as a data series and automatically
creates the correct chart. After creating the chart, you-can-enter the text Year into cell Al if
you like.
Let's create one more cool pie chart.
5. Select the range Al:D1, hold down CTRL and select the range A3:D3.
c

201 -54 54
2019
2020
2021

6. Create the pie chart (repeat steps 2-3).

7. Click the legend at the bottom and press Delete.
8. Select the pie
9. Click the + button on the right side of the chart and click the check box next to
Data Labels.

1 0. Click the paintbrush icon on the right side of the chart and change the color scheme of
the pie chart.

60
Result:

1 1. Right click the pie chart and click Format Data Labels.
1 2. Check Category Name, uncheck Value, check Percentage and click Center.

HOW TO CREATE A COMBINED CHART LIKE: COLUMN AND

LINE, COLUMN AND AREA

61
Suppose I have the data set as shown below and I want to plot both the revenue and profit
margin numbers in the same chart.

To create this combination chart, I first need to create a regular chart where I have all the
above data plotted on it.

Below are the steps to create a regular chart using the above data (the snapshots are
of Excel 2016):

1. Select the Revenue and Profit Margin data (Bl :C6 in this example).
2. Click the Insert tab.

3.

4. Click on Clustered Column chart. This will insert the chart in the worksheet
area.

62
 5. With the Chart Selected, go to the Design tab and click on Select Data.

6. In the 'Select Data Source' dialog box, click on Edit option (below the
'Horizontal (Category) Axis Labels').

7. Select years that you want to plot in the chart (A2:A6 in this example).

8. Click OK.

The above steps would give you a chart that has revenue and profit margin plotted as a
clustered column chart. If you can't see the Profit Margin bar in the chart, it's because the
value of Profit Margin is very less as compared to the Revenue value (but it's there as we
can see it's listed in the legend in orange color).

63
Now to create a combination chart from this clustered column chart, follow the below steps:

Right-click on any of the bars.

2. Click on 'Change Series Chart Type

3. In the Change Chart Type dialog box, make sure Combo category is selected
(which it should be by default).

64
 Choose the chart
type and axis for your data senes:

S. In the drop-down, click on 'Line with Markers'

6. Check the Secondary Axis checkbox for Profit Margin.

Choose the chart type and axis for yotr data series:

7. Click OK.

The above steps would give you the combination chart (also called combo chart) as shown
below.

65
You can now further customize the chart (such as change the title, remove the grid lines,
etc.)

Another good use case of creating combination charts is when you have to show actual vs
target values in the same chart. You can create a simple clustered column chart, or create
something more fancy by converting one bar into markers. Click here to read how to create
an Actual vs Target combination charts in Excel.
Creating Combination Charts In Excel 2010

While the first part of creating a chart is the same in all versions of Excel, converting that
chart into a combination chart is done a bit differently in Excel 2010.

Below are the steps to convert a regular clustered column chart into a combo chart in Excel
2010:

Click on any of the Profit margin bars. This will select all bars for profit margin.

2. Right-click and select Format Data Series.

3. In the Format Data Series dialogue box, select Secondary Axis On the
Plot Series On group)

66
 This will plot Profit Margin Data in a secondary Axis. You would
be able to see a vertical axis on the right of the chart.

4. Right-click and select Change Series Chart Type

5. In the Change Chart Type dialogue box, select Line with Markers option.

HOW TO DISPLAY AN [MAGE IN CHART AREA

All versions of Microsoft Excel allow you to insert pictures stored anywhere on your
computer or another computer you are connected to. In Excel 2016 and Excel 201 3, you
can also add an image from web pages and online storages such as OneDrive, Facebook
and Flickr.

Insert an image from a computer

67
Inserting a picture stored on your computer into your Excel worksheet is easy. All you have
to do is these 3 quick steps:

1. In your Excel spreadsheet, click where you want to put a picture.

2. Switch to the Insert tab > Wustrations group, and click Pictures.
3. In the Insert
Picture dialog that
opens, browse to the
picture of interest,
select it, and click
rables Illustrations Insert. This will place
the picture near the selected cell, more precisely, the top left corner of the picture
will align with the top left corner of the cell.

To insert several images at a time, press and hold the Ctrl key while selecting pictures, and
then click Insert, like shown in the screenshot below:

Done! Now, you can re-position or resize your image, or you can tock the picture to a
certain cell in a way that it resizes, moves, hides and filters together with the associated
cell.

Add picture from the web, OneDrive or Facebook

In the recent versions of Excel 2016 or Excel 201 3, you can also add images from webpages
by using Bing Image Search. To have it done, perform these steps:

On the Inserttab, click the Online Picturz button:

68
 2. The following window will appear, you type what you're looking for into the
search box, and hit Enter.

3. In search results, click on the picture you like best to select it, and then click
You can also select a few images and have them inserted in your Excel
sheet in one go:

If you are looking for something specific, you can filter the found images by size, type, color
or license - just use one or more fl&s at the top of the search results.
Note. If you plan to distribute your Excel file to someone else, check the picture's copyright
to make sure you can legally use it.

Besides adding images from Bing search, you can insert a picture stored on your
OneDrive, Facebook or Flickr. For this, click the Online Pictures button on the Insert tab,
and then do one of the following:

Click Browse next or

69
 Click the or Flick' icon at the bottom of the window.

Insert Pictures

[Link] -

Note. If your OneDrive account does not appear in the Insert Pictureswindow, most likely
you are not signed in with your Microsoft account. To fix this, click in link at the upper right
corner of the Excel window.

Paste picture in Excel from another program

The easiest way to insert a picture in Excel from another application is this:
Select an image in another application, for example in Microsoft Paint, Word or
PowerPoint, and click Ctrl + C to copy it.
2. Switch back to Excel, select a cell where you want to put the image and press
Ctrl + V to paste it. Yep, it's that easy!

70