1 ACO COURSE – MS EXCEL RECORD MATERIALS
Practical 1: MS Excel – Home Tab: Font, Alignment, and Number Formatting
Procedure
Step 1:: Open MS Excel 2007 and create a table with the following data:
Employee Name Department Basic Salary Bonus (%) Total Salary
John Doe HR 30000 10
Jane Smith IT 45000 15
Emily Brown Marketing 40000 12
Mark Taylor Sales 35000 8
Step 2: Apply Font Formatting:
Select the header row and apply:
o Bold text.
o Change the font style to Arial and font size to 12 pt.
o Use a light blue fill color for the background.
o Change the text color to dark blue
blue.
Step 3: Apply Alignment:
Center-align the Bonus (%) column.
Right-align the Basic Salary and Total Salary columns.
Apply Middle Alignment to the entire table for vertical alignment.
Use Wrap Text for the Department column
n to ensure all data fits within the cells.
Step 4: Apply Number Formatting:
Format the Basic Salary and Total Salary columns as Currency (₹ symbol).
Format the Bonus (%) column as Percentage.
Increase decimal places for salary columns to 2 digits.
Step 5: Calculate Total Salary:
Use the formula =C2 + (C2 * D2/100) in the Total Salary column for all rows.
2 ACO COURSE – MS EXCEL RECORD MATERIALS
Practical 2: MS Excel – Creating and Customizing Charts
harts
Procedure
Step 1: Enter the Data in Excel
Open MS Excel .
Create the following table:
Region Sales (₹)
North 50,000
South 40,000
East 45,000
West 60,000
Step 2: Insert a Chart
Highlight the Region and Sales ((₹) columns.
Go to the Insert Tab → Click on Column Chart → Select Clustered Column Chart.
Chart
Step 3: Customize the Chart
Add a Chart Title:
o Go to Chart Tools → Click Layout Tab → Choose Chart Title → Type: "Regional Sales
Data".
Add Data Labels:
o Go to Chart Tools → Click Layout Tab → Choose Data Labels → Select Outside End.
Format the Chart:
o Change the chart style to a 3D Column Chart under the Design Tab.
o Apply a color scheme that matches the data.
Move the chart below the table for better placement.
Step 4: Save the Chart
Save the Excel file as "Regional Sales [Link]"
[Link]".
3 ACO COURSE – MS EXCEL RECORD MATERIALS
Practical 3: MS Excel – Filter, Sort, Data Validation, Subtotal, Goal Seek
Procedure
Step 1: Data Entry
Enter the following data into Excel:
Employee Name Department Salary (₹) Bonus (%) Joining Date
John Doe HR 30,000 10 01/01/2020
Jane Smith IT 45,000 12 05/03/2019
Emily Brown Marketing 40,000 8 15/06/2021
Mark Taylor Sales 35,000 5 20/07/2022
Anna Lee HR 50,000 10 10/09/2020
Step 2: Sorting Data
Sort by Department:
o Select the entire table.
o Go to the Data Tab → Click on Sort → Choose Department from the drop-down
drop list and select
Ascending order.
Sort by Salary:
o With the table still selected, sort by Salary (₹) in Descending order.
Step 3: Apply Filter
Select the header row.
Go to the Data Tab → Click Filter (a dropdown arrow will appear next to each column header).
Use the filter
ter options to display only HR department employees.
Step 4: Apply Data Validation
Select the Bonus (%) column.
Go to the Data Tab → Click Data Validation → Under the Settings Tab,, choose Whole Number.
Set the validation criteria to allow numbers between 0 and 30 for Bonus percentage.
After applying validation, enter a bonus percentage greater than 30 to see the validation error message.
Step 5: Insert Subtotal
Select the table.
4 ACO COURSE – MS EXCEL RECORD MATERIALS
Go to Data Tab → Click Subtotal
Subtotal.
In the dialog box, select Department as the column to group by and apply the Sum function for the
Salary (₹) column.
Click OK to insert subtotals.
Step 6: Goal Seek
Suppose you
u want to know the bonus percentage required for an employee to reach a specific total
salary.
Select a cell in the Bonus (%) column (e.g., Jane Smith’s bonus percentage).
Go to Data Tab → What-If
If Analysis → Click Goal Seek.
In the Goal Seek dialog:
o Set cell: Total Salary (in this case, cell for Jane Smith’s Total Salary).
o To value: Set the desired total salary (e.g., ₹55,000).
o By changing cell: Bonus (%)
(%).
Click OK to calculate the required bonus percentage.
Practical 4: MS Excel – Freeze Panes, Print Title, and Print Area
Procedure
Step 1: Data Entry
Enter the following sample data into Excel:
Employee Name Department Salary (₹) Bonus (%) Joining Date
John Doe HR 30,000 10 01/01/2020
Jane Smith IT 45,000 12 05/03/2019
Emily Brown Marketing 40,000 8 15/06/2021
Mark Taylor Sales 35,000 5 20/07/2022
Anna Lee HR 50,000 10 10/09/2020
Michael Johnson IT 55,000 12 12/11/2022
Sarah Williams Marketing 48,000 8 10/01/2023
David Wilson Sales 52,000 7 22/02/2023
Step 2: Apply Freeze Panes
Select the cell B2 (the cell below the header row).
5 ACO COURSE – MS EXCEL RECORD MATERIALS
Go to the View Tab → Click on Freeze Panes → Select Freeze Panes from the dropdown.
The top row (headers) and the first column will remain visible when you scroll down or to the right.
Step 3: Set Print Titles
Go to the Page Layout Tab → Click on Print Titles in the Page Setup group.
In the Page Setup dialog box:
o In the Rows to repeat at top field, enter $1:$1 (to repeat the first row as the title).
o Click OK.
This will ensure that when you print the sheet, the column headers will be repeated on every page.
Step 4: Set Print Area
Select the area you want to print. For this example, select the entire data range (A1:E9).
Go to the Page Layout Tab → Click on Print Area → Select Set Print Area.
Now, when you print, only the sel
selected
ected range will be printed, excluding any empty cells outside the
selection.
Step 5: Print Preview
Go to the File Tab → Click on Print → Select Print Preview to ensure that the print area, titles, and
freeze panes appear as expected.
Check that the data repeats the header row on each page and that only the set print area will be printed.
Practical 5: MS Excel – Important Functions
Procedure
Step 1: Data Entry
Enter the following data into Excel:
Employee Name Department Salary (₹) Bonus (%) Joining Date
John Doe HR 30,000 10 01/01/2020
Jane Smith IT 45,000 12 05/03/2019
Emily Brown Marketing 40,000 8 15/06/2021
Mark Taylor Sales 35,000 5 20/07/2022
Anna Lee HR 50,000 10 10/09/2020
Step 2: SUM Function
To calculate the Total Salary (Salary + Bonus):
6 ACO COURSE – MS EXCEL RECORD MATERIALS
o In a new column, use the formula:
=C2 + (C2 * D2 / 100)
o This formula adds the salary and the calculated bonus.
o Copy this formula down for all employees.
For total salary across all employees, use the SUM function:
o Formula: =SUM(C2:C9)
o This sums the values in the Salary (₹) column.
Step 3: AVERAGE Function
To calculate the Averagee Salary across all employees:
o Formula: =AVERAGE(C2:C9)
o This formula calculates the average salary of all employees in the Salary (₹)
( column.
Step 4: MAX and MIN Functions
To find the Maximum Salary:
o Formula: =MAX(C2:C9)
o This finds the highest salary from the Salary (₹) column.
To find the Minimum Salary:
o Formula: =MIN(C2:C9)
o This finds the lowest salary from the Salary (₹) column.
Step 5: COUNT Functions
To count the number of employees with numerical values in the Salary column:
column
o Formula: =COUNT(C2:C9)
o This counts only the numeric values in the Salary (₹) column.
Step 6: IF Function
To apply a condition and calculate a Bonus eligibility (e.g., if salary > ₹45,000, bonus = ₹10%, else
bonus = ₹5%):
o Formula: =IF(C2 > 45000,
000, C2 * 10 / 100, C2 * 5 / 100)
o This applies the condition and calculates the bonus based on salary.