MS Excel Tutorial and Study Material - Udgam Welfare Foundation

1. What is a cell reference in Excel?

A cell reference identifies the location of a cell using its column letter and row number (e.g., B3). For example, if cell B3 contains 25, you can use =B3+10 to add 10 to it.

2. How do you differentiate between a relative, absolute, and mixed cell reference?

Relative: Changes when copied (e.g., A1).
Absolute: Stays fixed when copied ($A$1).
Mixed: Locks either row or column (A$1 or $A1).
Example: If B1 is $A$1+5, copying it won’t change $A$1.

3. How do you insert a new row or column in Excel?

Right-click a row number or column letter and choose Insert, or use Ctrl+Shift+=. For example, right-clicking row 5 and inserting will push the current row 5 data down.

4. What’s the difference between a workbook and a worksheet?

A workbook is the entire Excel file (.xlsx), and a worksheet is a single tab within it. For example, a budget workbook might have worksheets for “January,” “February,” and “March.”

5. How do you rename a worksheet?

Double-click the worksheet tab and type a new name, or right-click → Rename. For example, rename “Sheet1” to “Sales_2025” for clarity.

6. How can you quickly move to the last filled cell in a column?

Select a cell in the column and press Ctrl+Down Arrow. For example, if column A has data in A1 to A500, pressing it from A1 jumps to A500.

7. What does the “Wrap Text” option do?

Wrap Text makes long text visible on multiple lines within the same cell. For example, typing “Quarterly Sales Summary” in a narrow cell and enabling Wrap Text breaks it into lines without changing column width.

8. How do you freeze rows or columns in Excel?

Go to View → Freeze Panes. Example: To keep the header row visible, click cell A2 and choose Freeze Panes → Freeze Top Row, so it stays visible when scrolling.

9. What is the shortcut for saving a file in Excel?

Use Ctrl+S to save changes instantly. For example, after entering data in a budget sheet, pressing Ctrl+S stores the latest updates without opening the Save dialog.

10. How do you adjust the width of a column to fit its contents?

Double-click the column border in the header area or go to Home → Format → AutoFit Column Width. Example: If a cell shows “####” because it’s too narrow, AutoFit fixes it.

11. What’s the difference between copying and filling cells?

Copying duplicates data exactly, while filling (using the fill handle) extends a pattern. Example: Copying 1, 2 pastes 1, 2; filling them drags 3, 4, 5....

12. How can you undo and redo actions in Excel?

Undo: Ctrl+Z reverses your last action.
Redo: Ctrl+Y repeats the undone action.
Example: Delete a cell accidentally → Ctrl+Z restores it; Ctrl+Y deletes again.

13. How do you hide and unhide rows or columns?

Right-click the row/column header → Hide. To unhide, select surrounding rows/columns, right-click → Unhide. Example: Hide confidential salary data in column D.

14. How do you insert a hyperlink into a cell?

Right-click cell → Link (or Ctrl+K), enter a URL or file path. Example: In cell A1, insert hyperlink to https://openai.com for quick access.

15. What is the difference between “Save” and “Save As”?

Save updates the current file.
Save As creates a copy with a new name/location.
Example: Save As lets you duplicate “Report.xlsx” as “Report_Final.xlsx.”

16. How do you select multiple non-adjacent cells?

Hold Ctrl and click each cell you want. Example: To bold A1, C1, and E1 at once, select them with Ctrl+Click, then format.

17. What does the “Format Painter” tool do?

It copies formatting (font, color, borders) from one cell to others. Example: If A1 has bold, blue text, use Format Painter to apply the same style to A5.

18. How do you add comments or notes to cells?

Right-click cell → New Comment (or New Note in newer versions). Example: Add a comment in cell B2 saying “Check this formula for accuracy.”

19. How do you check spelling in Excel?

Go to Review → Spelling or press F7. Example: If you type “Accont” instead of “Account,” F7 suggests the correction.

20. How do you search for specific data in a worksheet?

Press Ctrl+F, type the keyword, and click Find Next. Example: Search for “Invoice 123” to locate it in a long billing sheet.

B. Formulas & Functions (Intermediate)

21. What is the difference between a formula and a function?

A formula is a user-created expression like =A1+B1, while a function is a predefined formula like =SUM(A1:B1). Functions save time for common tasks like summing, averaging, or looking up values.

22. How do you use the SUM function?

The SUM function adds numbers in a range. Syntax: =SUM(A1:A5). Example: If cells A1–A5 contain 2, 4, 6, 8, and 10, the result is 30.

23. What is the syntax for the IF function?

=IF(logical_test, value_if_true, value_if_false). Example: =IF(A1>50, "Pass", "Fail") returns Pass if A1 is greater than 50, otherwise returns Fail.

24. How do you calculate an average in Excel?

Use =AVERAGE(range). Example: =AVERAGE(B1:B4) with values 10, 20, 30, and 40 returns 25, which is the arithmetic mean.

25. How do you count the number of numeric values in a range?

Use =COUNT(range). Example: =COUNT(A1:A6) returns 4 if the range contains {10, text, 15, 20, blank, 5}.

26. What’s the difference between COUNT, COUNTA, COUNTIF, and COUNTIFS?

COUNT: Counts numbers
COUNTA: Counts all non-blank cells
COUNTIF: Counts cells meeting one condition
COUNTIFS: Counts cells meeting multiple conditions

Example: =COUNTIF(A1:A5, ">10") counts values above 10.

27. How do you use the VLOOKUP function?

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
Example: =VLOOKUP(101, A2:C10, 2, FALSE) finds ID 101 in column A and returns the matching value from column B.

28. How do you use HLOOKUP and when is it better than VLOOKUP?

HLOOKUP searches horizontally across a row.
Example: =HLOOKUP("Sales", A1:F2, 2, FALSE) looks in row 1 for “Sales” and returns data from row 2. Use it for horizontally arranged data.

29. How do you use INDEX and MATCH together?

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0)).
Example: =INDEX(C2:C10, MATCH(101, A2:A10, 0)) finds ID 101 in column A and returns its corresponding column C value.

30. What is the difference between an absolute and relative reference in formulas?

Relative: Changes when copied (A1).
Absolute: Stays fixed ($A$1).
Example: In a formula copied down, A1 becomes A2, but $A$1 stays $A$1.

31. How do you calculate the difference between two dates?

Simply subtract them: =B1-A1. Example: If A1 is 01-Jan-2025 and B1 is 10-Jan-2025, the formula returns 9 days difference.

32. How do you use the TEXT function to format numbers?

Syntax: =TEXT(value, format_text). Example: =TEXT(0.85, "0%") displays 85%, and =TEXT(TODAY(), "dd-mmm-yyyy") shows 13-Aug-2025.

33. What is the syntax for CONCAT (or CONCATENATE)?

CONCATENATE: =CONCATENATE(text1, text2, …)
CONCAT (newer): =CONCAT(text1, text2, …)
Example: =CONCAT(A1, " ", B1) joins first name in A1 with last name in B1.

34. How do you use LEFT, RIGHT, and MID functions?

LEFT(text, num_chars): Extracts characters from the left
RIGHT(text, num_chars): Extracts from the right
MID(text, start_num, num_chars): Extracts from the middle

Example: =LEFT("Excel", 3) returns Exc.

35. How do you remove extra spaces using TRIM?

=TRIM(text) removes extra spaces except single spaces between words. Example: =TRIM(" Excel Tips ") returns Excel Tips.

36. How do you round numbers using ROUND, ROUNDUP, and ROUNDDOWN?

ROUND: Rounds normally
ROUNDUP: Always rounds up
ROUNDDOWN: Always rounds down

Example: =ROUND(4.56, 1) gives 4.6, ROUNDUP gives 4.6, and ROUNDDOWN gives 4.5.

37. How do you find the largest and smallest values in a range?

Use =MAX(range) and =MIN(range). Example: In {10, 25, 5}, MAX returns 25 and MIN returns 5.

38. How do you use logical functions like AND and OR?

AND returns TRUE if all conditions are true
OR returns TRUE if any condition is true

Example: =AND(A1>10, B1<5) checks both conditions.

39. How do you create nested IF statements?

An IF inside another IF. Example:
=IF(A1>80, "High", IF(A1>50, "Medium", "Low")) categorizes values as High, Medium, or Low.

40. How do you calculate percentage change between two numbers?

Formula: =(New - Old) / Old * 100. Example: If sales go from 200 to 250, =(250-200)/200*100 gives 25%.

C. Data Management (Advanced)

41. How do you create and use named ranges?

Select the cells → Go to Formulas → Define Name → Enter a name (e.g., Sales2025). Then use it in formulas like =SUM(Sales2025) instead of =SUM(A1:A10).

42. How do you sort data in ascending or descending order?

Select the range → Go to Data → Sort A to Z (ascending) or Z to A (descending). Example: Sort names alphabetically or sales amounts from highest to lowest.

43. What's the difference between a custom sort and a normal sort?

Normal Sort: Sorts alphabetically or numerically
Custom Sort: Uses a defined order (e.g., "High, Medium, Low")

Example: Custom sort can arrange months as Jan–Dec instead of alphabetical.

44. How do you filter data in Excel?

Select the header row → Go to Data → Filter → Use dropdown arrows to choose criteria. Example: Filter sales data to show only transactions from "New York".

45. How do you use the Advanced Filter feature?

Set a criteria range with matching column headers → Go to Data → Advanced → Choose Filter in Place or Copy to Another Location. Example: Extract only sales above \$500 into a separate area.

46. How do you remove duplicate values from a dataset?

Select the data → Go to Data → Remove Duplicates → Select columns to check → Click OK. Example: Remove duplicate customer names to keep a clean mailing list.

47. How do you use Data Validation for dropdown lists?

Select cells → Data → Data Validation → Allow: List → Enter options or select a range. Example: Create a dropdown with choices like "Yes, No, Maybe" in column B.

48. How do you protect a worksheet with a password?

Go to Review → Protect Sheet → Set a password and select allowed actions. Example: Lock a financial sheet so others can't edit it without your password.

49. How do you protect specific cells while allowing others to be edited?

Select the cells you want editable → Right-click → Format Cells → Protection → Uncheck Locked → Protect Sheet. Example: Keep headers locked but allow data entry in the table.

50. How do you use conditional formatting for highlighting cells based on rules?

Select range → Home → Conditional Formatting → New Rule → Define condition. Example: Highlight all sales over \$1,000 in green using Cell Value > 1000.

51. How do you create icon sets with conditional formatting?

Select range → Home → Conditional Formatting → Icon Sets → Choose style (e.g., traffic lights). Example: Use green/yellow/red arrows for high/medium/low sales.

52. How do you create and refresh a pivot table?

Select data → Insert → PivotTable → Place in new/existing worksheet → Drag fields into Rows, Columns, Values. To refresh, right-click PivotTable → Refresh. Example: Summarize sales by region.

53. How do you group data in pivot tables?

Right-click a value (like a date or number) → Group → Choose intervals (e.g., months, years). Example: Group daily sales into monthly totals in a pivot table.

54. How do you create a pivot chart?

Create a PivotTable → Select it → Insert → PivotChart → Choose chart type. Example: Display pivot table sales summary as a bar chart.

55. What is the difference between a slicer and a filter in pivot tables?

Filter: Dropdown-based selection in the pivot table
Slicer: Visual, clickable buttons for filtering

Example: Use slicers for quick and interactive dashboard filters.

56. How do you use GETPIVOTDATA function?

=GETPIVOTDATA(data_field, pivot_table, [field1, item1], …). Example: =GETPIVOTDATA("Sales",$A$3,"Region","East") retrieves sales for East from a pivot table at A3.

57. How do you create a table from a range?

Select range → Insert → Table → Check "My table has headers." Example: Convert A1:C20 into a table for easier sorting, filtering, and formatting.

58. How do you use structured references in Excel tables?

In a table, use column names instead of cell references. Example: =SUM(Table1[Sales]) sums all values in the Sales column of Table1.

59. How do you link data between worksheets?

Type = in a cell → Navigate to another sheet → Select a cell → Press Enter. Example: =Sheet2!B5 pulls the value from cell B5 in Sheet2.

60. How do you consolidate data from multiple sheets?

Go to Data → Consolidate → Choose function (SUM, AVERAGE, etc.) → Add references from multiple sheets. Example: Combine sales totals from Jan, Feb, and Mar sheets into one summary.

D. Charts & Visualization (Pro Level)

61. How do you insert a basic column chart?

Select your data range → Insert → Column or Bar Chart → Choose a style (e.g., Clustered Column). Example: Show monthly sales data with months on the X-axis and sales on the Y-axis.

62. What are the steps to create a combo chart?

Select data → Insert → Combo Chart → Choose types for each series (e.g., Column for Sales, Line for Profit) → Click OK. Example: Display sales bars with a profit line overlay.

63. How do you add data labels to a chart?

Select the chart → Chart Elements (+) → Data Labels → Choose position (inside, outside, above). Example: Show actual sales values above each column in a bar chart.

64. How do you change the chart type after it's created?

Right-click chart → Change Chart Type → Select a new style. Example: Convert an existing bar chart into a line chart to better show trends over time.

65. What's the difference between a line chart and an area chart?

Line Chart: Shows trends with a line, focusing on values
Area Chart: Fills the area under the line, emphasizing magnitude

Example: Use line for precise trends, area for volume emphasis.

66. How do you create a scatter plot?

Select two sets of numeric data → Insert → Scatter (X, Y) → Choose style. Example: Plot "Hours Studied" vs "Exam Score" to see correlation.

67. How do you use sparklines?

Select a cell → Insert → Sparklines (Line/Column/Win-Loss) → Choose data range. Example: Add mini trendlines inside cells to quickly visualize monthly sales patterns.

68. How do you customize chart colors and themes?

Select chart → Chart Tools → Design → Change Colors or use Format options. Example: Apply brand colors to match corporate presentation style.

69. How do you change the axis scale on a chart?

Right-click axis → Format Axis → Adjust minimum, maximum, and unit values. Example: Set Y-axis minimum to 50 to better focus on high-value sales ranges.

70. How do you add a secondary axis to a chart?

Right-click a data series → Format Data Series → Secondary Axis. Example: Show revenue on the primary axis and profit margin (%) on the secondary axis for better comparison.

71. How do you create a histogram in Excel?

Select data → Insert → Statistic Chart → Histogram → Adjust bin size in Format Axis. Example: Show frequency distribution of test scores grouped into ranges.

72. How do you insert a trendline into a chart?

Select chart → Chart Elements (+) → Trendline → Choose type (Linear, Exponential). Example: Add a linear trendline to sales data to forecast future growth.

73. How do you create a waterfall chart?

Select data → Insert → Waterfall or Stock Chart → Waterfall. Example: Visualize how starting revenue is affected by various income and expense items to reach net profit.

74. How do you create a Gantt chart using Excel?

Create task list with start dates and durations → Insert Stacked Bar Chart → Format first series as invisible. Example: Show project schedule visually over a timeline.

75. How do you use conditional formatting with formulas for visual data cues?

Select range → Home → Conditional Formatting → New Rule → Use Formula. Example: =B2>1000 highlights high sales figures in green automatically.

76. How do you create heatmaps in Excel?

Select range → Conditional Formatting → Color Scales → Choose gradient style. Example: Darker colors show higher sales regions, lighter show lower sales.

77. How do you make a dynamic chart that updates automatically?

Use a table (Insert → Table) as chart source, or define a dynamic named range using OFFSET. Example: Add new sales rows and chart updates instantly.

78. How do you use camera tool in Excel?

Enable from Quick Access Toolbar → Select range → Click Camera → Paste as a live image. Example: Place a live-updating chart on a dashboard sheet.

79. How do you create a bullet chart?

Create a bar chart with actual value, add target line as secondary series, and format. Example: Show actual vs. target sales performance visually.

80. How do you create interactive dashboards in Excel?

Combine pivot tables, pivot charts, slicers, timelines, and formulas into a single sheet. Example: An executive dashboard showing real-time sales by product, region, and salesperson.

E. Automation, Analysis & Professional Use (Expert)

81. How do you record a macro in Excel?

Go to View → Macros → Record Macro → Name it, choose storage location → Perform actions → Stop Recording. Example: Record steps to format a sales table automatically.

82. How do you run a recorded macro?

Go to View → Macros → View Macros → Select macro → Click Run. Example: Run FormatReport macro to instantly style a monthly report.

83. How do you assign a macro to a button?

Insert a shape or Form Control button → Right-click → Assign Macro → Select macro → OK. Example: Click a "Generate Report" button to run your automation.

84. How do you open and use the VBA editor?

Press Alt+F11 to open VBA editor → Use Insert → Module to write code. Example: Create a macro to insert the current date in cell A1 automatically.

85. How do you write a simple VBA macro to format a range?

In VBA editor:

Sub FormatRange()
    Range("A1:D10").Font.Bold = True
    Range("A1:D10").Interior.Color = vbYellow
End Sub

Example: Makes A1:D10 bold with yellow background.

86. How do you use the Goal Seek tool?

Go to Data → What-If Analysis → Goal Seek → Set Cell, Target Value, Changing Cell. Example: Find sales needed to achieve \$10,000 profit by changing price.

87. How do you use the Solver add-in?

Enable Solver in Add-ins → Data → Solver → Set Objective, Variables, and Constraints → Solve. Example: Maximize profit by changing units produced under resource limits.

88. How do you create scenarios using Scenario Manager?

Go to Data → What-If Analysis → Scenario Manager → Add scenarios with different variable values → Show results. Example: Compare "Best Case" and "Worst Case" budget outcomes.

89. How do you use Power Query to import data?

Go to Data → Get Data → Choose source (Excel, CSV, Web, Database) → Load or Transform in Power Query Editor. Example: Import sales data from a CSV file.

90. How do you clean data using Power Query?

In Power Query: Remove duplicates, split columns, trim spaces, change data types, filter rows. Example: Clean customer names and standardize date formats before loading to Excel.

91. How do you merge queries in Power Query?

In Power Query: Select Merge Queries → Choose tables and matching columns → Select Join type (e.g., Left Join) → Load results. Example: Merge customers and orders tables by Customer ID.

92. How do you use Power Pivot for large data analysis?

Enable Power Pivot → Load large datasets → Create relationships → Use DAX formulas → Build PivotTables. Example: Analyze millions of sales records efficiently with no slowdown.

93. How do you create relationships between tables in Power Pivot?

In Power Pivot → Diagram View → Drag matching field from one table to another. Example: Link Sales table's CustomerID to Customers table's CustomerID.

94. How do you use DAX formulas in Power Pivot?

In Power Pivot → Add column or measure using DAX (Data Analysis Expressions). Example: TotalSales := SUM(Sales[Amount]) calculates total sales across the model.

95. How do you refresh all data connections at once?

Go to Data → Refresh All or press Ctrl+Alt+F5. Example: Refresh pivot tables, Power Query imports, and external connections with one click.

96. How do you create a custom function using VBA?

In VBA editor → Insert Module → Write function:

Function SquareNum(x As Double) As Double
    SquareNum = x ^ 2
End Function

Example: =SquareNum(5) returns 25.

97. How do you handle circular references in Excel?

Enable iterative calculation in File → Options → Formulas → Set maximum iterations. Example: Used in mortgage models where interest depends on closing balance and vice versa.

98. How do you link Excel with other applications like Word or PowerPoint?

Copy data → In Word/PowerPoint → Paste Special → Paste Link. Example: Linked chart in PowerPoint updates when Excel data changes.

99. How do you import data from an external database into Excel?

Go to Data → Get Data → From Database → Choose database type → Enter credentials → Load into Excel. Example: Import sales orders from SQL Server.

100. How do you optimize large Excel files for faster performance?

Use tables instead of entire column references, remove unnecessary formatting, limit volatile functions, use Power Query/Power Pivot for large datasets. Example: Reduced 50MB sales file to 10MB and improved speed.