AI Reading

Quick summary of this article

Preparing the AI summary…

Advanced Excel Chapter 1: Professional Excel Foundations

Microsoft Excel is one of the most widely used tools for data management, reporting, accounting, administration and business analysis. Before learning advanced formulas, PivotTables, Power Query or dashboards, you must understand how to structure and control an Excel workbook professionally.

In this chapter, you will learn about workbook structure, worksheets, cells, different types of cell references, Excel Tables, named ranges, sorting, filtering, workbook protection and important keyboard shortcuts. You will also complete a practical sales-data exercise.

Learning Objectives

After completing this chapter, you should be able to:

Understand the structure of an Excel workbook.
Create and manage multiple worksheets.
Use relative, absolute and mixed cell references.
Convert ordinary data into a professional Excel Table.
Use structured references in formulas.
Create and manage named ranges.
Sort and filter business data correctly.
Use Custom Views for different reporting requirements.
Protect worksheets, formulas and workbook structures.
Use important keyboard shortcuts to work faster.

1. Understanding the Excel Workbook Structure

An Excel file is known as a workbook. A workbook can contain one or more worksheets. Each worksheet contains rows, columns and cells in which data can be stored and calculated.

Important Excel Components

Workbook: The complete Excel file saved with an extension such as .xlsx, .xlsm or .xls.
Worksheet: An individual spreadsheet page inside the workbook.
Row: A horizontal group of cells identified by numbers such as 1, 2, 3 and 4.
Column: A vertical group of cells identified by letters such as A, B, C and D.
Cell: The intersection of a row and a column.
Cell address: The unique location of a cell, such as A1, C5 or F20.
Range: A collection of cells, such as A1:D10.
Active cell: The currently selected cell in the worksheet.
Formula Bar: The area used to view, enter or edit cell content and formulas.
Name Box: The box that displays the address or defined name of the selected cell.

Common Excel File Extensions

Extension	File type	Purpose
`.xlsx`	Excel Workbook	Standard workbook without VBA macros
`.xlsm`	Macro-Enabled Workbook	Workbook containing VBA macros
`.xlsb`	Excel Binary Workbook	Useful for large workbooks requiring faster performance
`.csv`	Comma-Separated Values	Plain-text tabular data without formulas or formatting
`.xltx`	Excel Template	Reusable workbook template

2. Planning a Professional Workbook

A professional workbook should not store everything on one worksheet. Data input, calculations and reports should be separated so that the workbook remains understandable and manageable.

Recommended Workbook Structure


Advanced_Sales_Report.xlsx

├── Instructions
├── Raw_Data
├── Product_Master
├── Customer_Master
├── Calculations
├── Pivot_Report
└── Dashboard

Purpose of Each Worksheet

Instructions: Explains how the workbook should be used.
Raw_Data: Stores original transactional data.
Product_Master: Stores product names, categories and prices.
Customer_Master: Stores customer-related information.
Calculations: Contains formulas and supporting calculations.
Pivot_Report: Contains PivotTables and summarized information.
Dashboard: Displays KPIs, charts and management insights.

Professional Worksheet Naming Rules

Use short and meaningful worksheet names.
Avoid names such as Sheet1, Sheet2 and New Sheet.
Use names such as Sales_Data, Expenses and Dashboard.
A worksheet name cannot contain / \ ? * [ ] :.
Use consistent naming throughout the workbook.

3. Creating the Practice Sales Dataset

Create a worksheet named Sales_Data and enter the following sample records. This dataset will be used throughout the chapter.

OrderID	OrderDate	Salesperson	Region	Product	Quantity	UnitPrice	TotalSales
ORD-1001	01-Jul-2026	Rahul	East	Laptop	2	45000	90000
ORD-1002	02-Jul-2026	Priya	North	Monitor	4	12000	48000
ORD-1003	03-Jul-2026	Imran	West	Keyboard	10	1500	15000
ORD-1004	04-Jul-2026	Neha	South	Printer	3	18500	55500
ORD-1005	05-Jul-2026	Rahul	East	Mouse	15	800	12000
ORD-1006	06-Jul-2026	Priya	North	Laptop	1	45000	45000
ORD-1007	07-Jul-2026	Imran	West	Monitor	5	12000	60000
ORD-1008	08-Jul-2026	Neha	South	Keyboard	8	1500	12000

Calculate Total Sales

If Quantity is stored in column F and Unit Price is stored in column G, enter the following formula in cell H2:

=F2*G2

Press Enter and copy the formula down to the remaining rows. Excel will automatically adjust the row numbers for each record.

4. Relative Cell References

A relative reference changes automatically when a formula is copied to another cell. It is the default reference type used by Excel.

Example

=F2*G2

When this formula is copied from row 2 to row 3, Excel changes it automatically:

=F3*G3

When copied to row 4, it becomes:

=F4*G4

When to Use Relative References

Calculating the total amount for every transaction.
Calculating employee salaries row by row.
Calculating student totals or percentages.
Applying the same calculation to multiple records.

5. Absolute Cell References

An absolute reference remains fixed when a formula is copied. Dollar signs are placed before both the column letter and row number.

$B$1

GST Calculation Example

Suppose the GST rate of 18% is stored in cell K1. To calculate GST on the sales amount in H2, use:

=H2*$K$1

When the formula is copied down, H2 changes to H3, H4 and so on. However, $K$1 remains fixed.

Complete GST Calculation


GST Amount = H2*$K$1

Final Amount = H2+(H2*$K$1)

When to Use Absolute References

GST, tax or commission rates
Currency conversion rates
Discount percentages
Fixed targets
Interest rates
Constants used in multiple formulas

6. Mixed Cell References

A mixed reference fixes either the column or the row, but not both. Mixed references are especially useful when formulas are copied across rows and columns.

Types of Mixed References

Reference	Fixed part	Changing part
`$A1`	Column A	Row number
`A$1`	Row 1	Column letter

Multiplication Table Example

Suppose numbers are entered vertically in column A and horizontally in row 1. Use the following formula to generate a multiplication table:

=$A2*B$1

In this formula, column A remains fixed while the row can change. Row 1 remains fixed while the column can change.

Switching Reference Types with F4

While editing a reference inside a formula, press F4 repeatedly to switch between the available reference types:


A1
$A$1
A$1
$A1

7. Converting Data into an Excel Table

An Excel Table is a structured and dynamic data range. Tables automatically expand when new records are added and make formulas easier to understand.

Steps to Create an Excel Table

Select any cell inside the sales dataset.
Press Ctrl + T.
Confirm the data range.
Select My table has headers.
Click OK.
Open the Table Design tab.
Rename the table to SalesData.

Benefits of Excel Tables

Automatic expansion when new data is added
Built-in sorting and filtering
Consistent formatting
Automatic formula copying
Structured references
Optional Total Row
Better compatibility with PivotTables and Power Query

8. Using Structured References

When data is converted into an Excel Table, formulas can refer to column names instead of ordinary cell addresses.

Ordinary Cell Formula

=F2*G2

Structured Reference Formula

=[@Quantity]*[@UnitPrice]

The @ symbol means that Excel should use values from the current row.

Calculate Total Sales from the Entire Table

=SUM(SalesData[TotalSales])

Calculate Average Unit Price

=AVERAGE(SalesData[UnitPrice])

Count the Number of Orders

=COUNTA(SalesData[OrderID])

Find the Maximum Sale

=MAX(SalesData[TotalSales])

Why Structured References Are Better

Formulas are easier to read.
References automatically include new rows.
Column names make formulas self-explanatory.
There is less risk of selecting an incorrect range.

9. Creating Named Ranges

A named range allows you to assign a meaningful name to a cell or range. For example, instead of referring to $K$1, you can name that cell GST_Rate.

Steps to Create a Named Range

Select the cell containing the GST rate.
Click the Name Box on the left side of the Formula Bar.
Type GST_Rate.
Press Enter.

Formula Using an Ordinary Reference

=H2*$K$1

Formula Using a Named Range

=H2*GST_Rate

Rules for Naming Ranges

A name must begin with a letter, underscore or backslash.
Spaces are not allowed.
Use an underscore instead of a space, such as Discount_Rate.
A name cannot look like a cell address.
Use meaningful names such as Sales_Target or Tax_Rate.

Managing Named Ranges

Go to Formulas → Name Manager to view, edit or delete named ranges. The Name Manager also shows the cell or range associated with each name.

10. Advanced Sorting

Sorting rearranges data based on one or more columns. It does not remove any record from the dataset.

Single-Level Sorting

To arrange Total Sales from highest to lowest:

Click any cell in the TotalSales column.
Open the Data tab.
Select Sort Largest to Smallest.

Multi-Level Sorting

Suppose you want to arrange data first by Region and then by Total Sales.

Select any cell inside the dataset.
Go to Data → Sort.
Under Sort by, select Region.
Click Add Level.
Under Then by, select TotalSales.
Choose Largest to Smallest.
Click OK.

Useful Sorting Examples

Sort employees by department and salary.
Sort students by class and marks.
Sort orders by region and order date.
Sort products by category and sales value.
Sort tasks by priority and deadline.

11. Advanced Filtering

Filtering temporarily hides records that do not match the selected conditions. Unlike sorting, filtering does not rearrange the complete dataset.

Basic Filter Example

To display only sales from the East region:

Click the filter arrow in the Region column.
Clear Select All.
Select East.
Click OK.

Number Filter Example

To display orders where Total Sales is greater than ₹50,000:

Open the filter arrow in the TotalSales column.
Select Number Filters → Greater Than.
Enter 50000.
Click OK.

Date Filter Example

The Order Date column can be filtered using conditions such as:

Today
Yesterday
This Week
Last Month
This Quarter
Between two selected dates

Multiple-Condition Filter Example

You can filter the dataset to display only:

Region: East
Product: Laptop
Total Sales: Greater than ₹40,000

Excel applies these conditions together and displays only records that meet all selected requirements.

12. Using SUBTOTAL with Filtered Data

The normal SUM function includes rows that have been hidden by a filter. The SUBTOTAL function can calculate only the visible rows.

Total of Visible Sales

=SUBTOTAL(9,SalesData[TotalSales])

In this formula, 9 tells Excel to perform a SUM calculation.

Count Visible Records

=SUBTOTAL(3,SalesData[OrderID])

Average of Visible Values

=SUBTOTAL(1,SalesData[TotalSales])

These formulas automatically recalculate when filters are changed.

13. Creating Custom Views

Custom Views allow you to save different display, filter and print settings for the same worksheet. This is useful when different departments need different versions of a report.

Example Custom Views

Management View: Displays all regions and total sales.
East Region View: Displays only East region records.
High-Value Orders: Displays sales greater than ₹50,000.
Print View: Uses a specific print area and page layout.

Steps to Create a Custom View

Apply the required filters and display settings.
Go to View → Custom Views.
Click Add.
Enter a meaningful name.
Select the required settings.
Click OK.

Important: Custom Views may be unavailable when the workbook contains Excel Tables. If this happens, use separate worksheets, slicers or saved filter arrangements instead.

14. Workbook and Worksheet Protection

Protection helps prevent accidental changes to formulas, worksheet structures and important business data. It should not be treated as a complete security or encryption system.

Protecting a Worksheet

Go to the Review tab.
Click Protect Sheet.
Enter a password if required.
Select the actions users are allowed to perform.
Click OK.
Confirm the password.

Allowing Data Entry but Protecting Formulas

Select the cells in which users are allowed to enter data.
Press Ctrl + 1 to open Format Cells.
Open the Protection tab.
Clear the Locked checkbox.
Click OK.
Go to Review → Protect Sheet.

Users will be able to edit the unlocked cells, while formula cells will remain protected.

Hiding Formulas

Select the formula cells.
Press Ctrl + 1.
Open the Protection tab.
Select Hidden.
Click OK.
Protect the worksheet.

Protecting Workbook Structure

Workbook structure protection prevents users from adding, deleting, renaming, moving or hiding worksheets.

Go to Review → Protect Workbook.
Select Structure.
Enter a password if required.
Click OK.

15. Essential Excel Keyboard Shortcuts

Shortcut	Purpose
`Ctrl + N`	Create a new workbook
`Ctrl + O`	Open a workbook
`Ctrl + S`	Save the workbook
`Ctrl + C`	Copy selected data
`Ctrl + X`	Cut selected data
`Ctrl + V`	Paste copied data
`Ctrl + Z`	Undo the previous action
`Ctrl + Y`	Repeat or redo an action
`Ctrl + T`	Convert a data range into an Excel Table
`Ctrl + Shift + L`	Enable or disable filters
`Ctrl + Arrow Key`	Move to the edge of a data region
`Ctrl + Shift + Arrow Key`	Select data up to the edge of a region
`Ctrl + Space`	Select the entire column
`Shift + Space`	Select the entire row
`Ctrl + 1`	Open the Format Cells dialog box
`Ctrl + ;`	Insert the current date
`Ctrl + Shift + ;`	Insert the current time
`Alt + =`	Insert AutoSum
`F2`	Edit the active cell
`F4`	Switch between reference types while editing a formula
`Ctrl + Page Up/Page Down`	Move between worksheets

16. Common Excel Mistakes to Avoid

Using blank rows and columns inside a dataset
Merging cells inside transactional data
Using different date formats in the same column
Mixing numbers and text in one column
Typing totals manually instead of using formulas
Using hard-coded values inside formulas unnecessarily
Applying formatting to complete columns without reason
Leaving worksheets with names such as Sheet1 and Sheet2
Deleting source data after creating a report
Failing to keep a backup copy of an important workbook
Using colour as the only way to identify information
Sharing an unprotected workbook containing sensitive formulas

17. Practical Project: Create a Professional Sales Workbook

Complete the following project to apply the concepts covered in this chapter.

Project Requirements

Create a workbook named Advanced_Sales_Report.xlsx.
Create worksheets named Instructions, Sales_Data, Calculations and Summary.
Enter at least 20 sales transactions in the Sales_Data worksheet.
Convert the dataset into an Excel Table.
Name the table SalesData.
Calculate Total Sales using structured references.
Create a GST rate cell and name it GST_Rate.
Calculate GST Amount and Final Amount.
Sort the records by Region and Total Sales.
Filter orders where Total Sales is greater than ₹50,000.
Use SUBTOTAL to calculate the visible sales amount.
Unlock the data-entry cells.
Protect the formula cells.
Protect the workbook structure.
Apply professional formatting and save the workbook.

Suggested Table Formulas


Total Sales:
=[@Quantity]*[@UnitPrice]

GST Amount:
=[@TotalSales]*GST_Rate

Final Amount:
=[@TotalSales]+[@[GST Amount]]

Grand Total:
=SUM(SalesData[Final Amount])

Visible Total:
=SUBTOTAL(9,SalesData[Final Amount])

18. Practice Exercises

Exercise 1: Cell References

Create a product-sales sheet and calculate the sales amount using relative references. Store a discount percentage in a separate cell and use an absolute reference to calculate the discount.

Exercise 2: Mixed References

Create a multiplication table from 1 to 10 using the following mixed-reference formula:

=$A2*B$1

Exercise 3: Excel Table

Create an employee dataset containing Employee ID, Name, Department, Basic Salary and Bonus. Convert the range into an Excel Table and calculate Total Salary using a structured reference.

=[@BasicSalary]+[@Bonus]

Exercise 4: Sorting and Filtering

Sort the employee dataset by Department and then by Total Salary from highest to lowest. Filter the records to display only employees with a salary greater than ₹40,000.

Exercise 5: Protection

Unlock the employee data-entry cells, keep formula cells locked and protect the worksheet. Test whether users can edit the formulas after protection.

19. Chapter Quiz

What is the difference between a workbook and a worksheet?
What does the reference $A$1 mean?
What is the difference between $A1 and A$1?
Which keyboard shortcut converts a range into an Excel Table?
What does the @ symbol mean in a structured reference?
Why should blank rows be avoided inside a dataset?
What is the purpose of the SUBTOTAL function?
What is the difference between sorting and filtering?
How can users be allowed to edit input cells while formulas remain protected?
Which feature prevents users from deleting or renaming worksheets?

20. Frequently Asked Questions

What is the difference between an Excel range and an Excel Table?

A normal range is a basic group of cells. An Excel Table is a structured and dynamic range that automatically expands, copies formulas, applies consistent formatting and supports structured references.

Why should I use absolute references?

Absolute references are used when a formula must always refer to the same cell, such as a tax rate, conversion rate, discount percentage or business target.

Can I use spaces in a named range?

No. Excel does not allow spaces in named ranges. You can use an underscore, such as Sales_Target.

Does worksheet protection make sensitive data completely secure?

No. Worksheet protection is mainly designed to prevent accidental modification. Sensitive files should also use appropriate file-level encryption, access control and secure storage.

Why should raw data and reports be kept on separate worksheets?

Separating raw data, calculations and reports makes the workbook easier to maintain, audit, refresh and troubleshoot. It also reduces the risk of accidentally changing source records.

Conclusion

Professional Excel work begins with a properly structured workbook. Understanding worksheets, cell references, Excel Tables, named ranges, sorting, filtering and protection provides the foundation required for advanced formulas, PivotTables, Power Query and dashboard development.

You should now be able to organize business data, create reliable formulas, control how data is displayed and protect important workbook elements. In the next chapter, we will study logical and error-handling functions such as IF, IFS, AND, OR, IFERROR and IFNA through practical business examples.

Advanced Excel Foundations: Chapter 1 with Examples