Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Data Types and Cell Formatting

Learn how Excel stores text, numbers, dates, currency, percentages, and formulas. Understand number formats, font styles, alignment, borders, fill colors, and professional spreadsheet presentation.

Learning objectives: Identify common Excel data types, apply suitable number formats, format cells professionally, and avoid common formatting mistakes.

1. What Are Data Types in Excel?

A data type describes the kind of information stored in a cell. Excel can work with text, numbers, dates, times, logical values, and formulas. Correctly entering information helps Excel calculate results and sort or filter data reliably.

For example, Samadhan may enter his name in column A, examination marks in column B, an examination date in column C, and a formula in column D to calculate a total.

CIIT Training Institute — Excel Data Student Name Marks Exam Date Total Result Samadhan 85 17-Oct-2026 175 Pass Samiksha 92 17-Oct-2026 180 Pass Pradnya 76 17-Oct-2026 160 Pass Omkar 68 17-Oct-2026 148 Pass Different columns store different kinds of information.

Illustration: a structured spreadsheet containing text, numeric values, dates, calculated totals, and results.

2. Common Data Types in Excel

Common Excel Data Types Aa Text Example: Samadhan 123 Number Example: 85 Date Date and Time 17-Oct-2026 ₹ Currency Example: ₹1,250.00 % Percentage Example: 75% fx Formula =SUM(B2:C2) Choose the appropriate format for the information you enter.

Illustration: common data types and their example values.

Type Example Typical Use
Text Samadhan Names, descriptions, labels
Number 85 Marks, quantities, counts
Date 17-Oct-2026 Joining dates, transactions, schedules
Currency ₹1,250.00 Fees, expenses, sales
Percentage 75% Growth, marks percentage, completion
Formula =SUM(B2:C2) Automatic calculations
Logical value TRUE / FALSE Conditions and logical tests

3. What Is Cell Formatting?

Cell formatting changes the appearance of data without necessarily changing the underlying value. It helps make worksheets readable, consistent, and professional.

Common formatting options include:

  • Font: change font family, size, bold, italic, and underline.
  • Font Color: change the color of text.
  • Fill Color: apply a background color to cells.
  • Borders: draw outlines or separators around cells.
  • Alignment: position text horizontally or vertically.
  • Number Format: display values as numbers, dates, currency, or percentages.
  • Wrap Text: show long text on multiple lines within a cell.
Before Formatting After Formatting Name Marks Result Samadhan 85 Pass Samiksha 92 Pass Name Marks Result Samadhan 85 Pass Samiksha 92 Pass Plain cells Clear headings and borders

Illustration: formatting improves readability while preserving the original data values.

4. How to Format Cells in Excel

  1. Select the cell or range of cells you want to format.
  2. Open the Home tab on the Excel ribbon.
  3. Use the Font section to change font, size, bold, or color.
  4. Use Alignment to position text and enable Wrap Text.
  5. Use the Number Format dropdown to choose an appropriate format.
  6. Use Borders and Fill Color to make headings and totals easier to identify.
Shortcut: Press Ctrl + 1 to open the Format Cells dialog in desktop Excel. It provides detailed controls for number formats, alignment, fonts, borders, and fill.

5. Understanding Number Formats

Number formatting controls how a numeric value appears on screen. In many cases, the underlying value remains the same.

Format Stored Value Example Displayed Example
General 1250 1250
Number 1250 1,250.00 (with two decimal places)
Currency 1250 ₹1,250.00, depending on regional settings
Percentage 0.75 75%
Date A date serial value 17-Oct-2026, depending on the selected format
Important: Applying Percentage format to the number 75 normally displays 7500%, not 75%. To display 75%, use the decimal value 0.75 before applying Percentage format.

6. Practical Example: Formatting Student Marks

Omkar is preparing a student marks report. He wants the headings to stand out, the marks to be easy to read, and the total column to use a formula.

Student Name Excel VBA Total Percentage
Samadhan 85 90 175 87.50%
Samiksha 92 88 180 90.00%
Pradnya 76 84 160 80.00%
Omkar 68 80 148 74.00%
Neha 88 95 183 91.50%

Formula 1: Calculate Total Marks

If Excel marks are in B2 and VBA marks are in C2, enter this formula in D2:

Excel Formula
=SUM(B2:C2)

The formula adds both marks. For Samadhan, the total is 175.

Formula 2: Calculate Percentage

If the maximum marks are 200, enter this formula in E2:

Excel Formula
=D2/200

Apply Percentage format to E2 and set two decimal places. For a total of 175, Excel displays 87.50%.

Recommended Formatting

  • Make the heading row bold and apply a dark green fill.
  • Use borders to separate the table columns.
  • Center-align numeric marks if appropriate.
  • Apply Percentage format to the percentage column.
  • Use consistent font sizes and avoid excessive colors.

7. Common Formatting Mistakes

  • Using text values where numeric calculations are required.
  • Displaying percentages incorrectly because the underlying value is wrong.
  • Using different fonts, colors, and alignments without a clear purpose.
  • Applying Currency format to values that are not monetary amounts.
  • Assuming that changing a cell's appearance always changes its stored value.
  • Using too many merged cells in datasets that need sorting and filtering.

8. Practice Exercise

Create and format a student marks worksheet.

  1. Enter the names Samadhan, Samiksha, Pradnya, Omkar, and Neha.
  2. Add two subject columns and enter sample marks.
  3. Calculate total marks with the SUM formula.
  4. Calculate percentages using the maximum possible marks.
  5. Format the heading row with bold text and a fill color.
  6. Apply borders and suitable number formats.
  7. Save the workbook and verify the displayed results.

Goal: Practice both data entry and professional cell formatting.

Important Points to Remember

  • Excel cells can contain text, numbers, dates, logical values, and formulas.
  • Number formatting changes the display of values in many cases, not the underlying value.
  • Percentage format displays 0.75 as 75%.
  • Cell formatting improves readability and report presentation.
  • Use formulas to calculate totals and percentages instead of manually entering results.

Summary

In this lesson, you learned about common Excel data types, including text, numbers, dates, currency, percentages, logical values, and formulas. You also learned how to apply cell formatting, use number formats, and prepare a professional student marks report.

Correct data entry and formatting help make spreadsheets reliable and readable. In the next topic, we will learn about Basic Excel Formulas and Operators.