Excel Basics & Navigation
1. What is Microsoft Excel?
Microsoft Excel is a spreadsheet application developed by Microsoft that allows users to organize, format, and calculate data using a grid of cells arranged in rows and columns. It is widely used for data analysis, financial modeling, reporting, and various business tasks across industries.
2. What is the difference between a workbook and a worksheet in Excel?
A workbook is the entire Excel file that can contain multiple worksheets, while a worksheet (or sheet) is a single page within a workbook consisting of rows and columns of cells. By default, a new Excel workbook opens with one worksheet, but you can add, rename, or delete worksheets as needed.
3. What is a cell reference in Excel?
A cell reference identifies a specific cell or range of cells in a worksheet, using the column letter followed by the row number (for example, A1 or B5). Cell references are used in formulas and functions to pull data from specific locations within the spreadsheet.
4. What is the difference between relative, absolute, and mixed cell references?
A relative reference (e.g., A1) changes when a formula is copied to another cell, adjusting based on its new position. An absolute reference (e.g., $A$1) remains fixed regardless of where the formula is copied, while a mixed reference (e.g., $A1 or A$1) locks either the column or the row but not both.
5. How do you name a cell or a range of cells in Excel?
You can name a cell or range by selecting it and typing a name in the Name Box to the left of the formula bar, or by going to Formulas > Define Name. Named ranges make formulas more readable and easier to maintain, for example using =SUM(Sales) instead of =SUM(B2:B100).
6. What are some essential keyboard shortcuts in Excel?
Key shortcuts include Ctrl+C (copy), Ctrl+V (paste), Ctrl+Z (undo), Ctrl+S (save), Ctrl+Home (go to cell A1), and Ctrl+End (go to the last used cell). Other important shortcuts are Ctrl+Shift+L (toggle filters), Ctrl+; (insert current date), and F2 (edit active cell).
7. What is the Freeze Panes feature in Excel?
Freeze Panes is a feature under the View tab that locks specific rows or columns in place so they remain visible while you scroll through the rest of the worksheet. You can freeze the top row, the first column, or both rows and columns above and to the left of a selected cell.
8. How do you hide and unhide rows or columns in Excel?
To hide rows or columns, select them, right-click, and choose Hide. To unhide them, select the adjacent rows or columns on either side, right-click, and choose Unhide. You can also use the Format menu under the Home tab to hide and unhide rows and columns.
9. What is the AutoFill feature in Excel?
AutoFill allows you to quickly fill a series of cells with data that follows a pattern, such as consecutive numbers, dates, or days of the week. You use it by entering the starting value(s), selecting the cell(s), and dragging the fill handle (the small square at the bottom-right corner of the selection) across the desired range.
10. What is the Format Painter and how does you use it?
The Format Painter is a tool on the Home tab that copies the formatting from one cell or range and applies it to another. You select the cell with the desired formatting, click the Format Painter button (or press Ctrl+Shift+C to copy formatting), and then click or drag across the target cells to apply it. Double-clicking the Format Painter allows you to apply the formatting to multiple non-adjacent ranges.
Formulas & Functions
11. What is the difference between a formula and a function in Excel?
A formula is any expression that begins with an equals sign (=) and performs a calculation, such as =A1+B1. A function is a predefined formula built into Excel that performs a specific operation, such as =SUM(A1:A10). All functions are formulas, but not all formulas use functions.
12. How do the SUM and AVERAGE functions work?
The SUM function adds up all the values in a specified range, for example =SUM(A1:A10) returns the total of cells A1 through A10. The AVERAGE function calculates the arithmetic mean of the values in a range, for example =AVERAGE(B1:B20) returns the sum of B1 through B20 divided by the count of numeric values in that range.
13. What is the difference between COUNT, COUNTA, and COUNTBLANK?
COUNT counts only the cells that contain numeric values in a range. COUNTA counts all non-empty cells regardless of data type, including text and errors. COUNTBLANK counts only the empty cells in a given range.
14. How do COUNTIF and SUMIF functions work?
COUNTIF counts the number of cells in a range that meet a specified condition, for example =COUNTIF(A1:A100, ">50") counts cells with values greater than 50. SUMIF adds up the values in a range that satisfy a given criterion, for example =SUMIF(A1:A10, "Yes", B1:B10) sums values in column B where the corresponding cell in column A equals "Yes."
15. How does the IF function work, and how do you nest IF statements?
The IF function evaluates a logical condition and returns one value if true and another if false, using the syntax =IF(condition, value_if_true, value_if_false). You can nest IF functions by placing another IF as the value_if_false argument, for example =IF(A1>=90, "A", IF(A1>=80, "B", "C")), to handle multiple conditions sequentially.
16. What are the AND and OR functions, and how are they used with IF?
The AND function returns TRUE only if all its conditions are true, while the OR function returns TRUE if at least one condition is true. They are commonly combined with IF, for example =IF(AND(A1>10, B1<100), "Valid", "Invalid") returns "Valid" only when both conditions are met.
17. What is the VLOOKUP function and what are its limitations?
VLOOKUP searches for a value in the first column of a table range and returns a corresponding value from a specified column, using the syntax =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Its main limitations are that it can only look up values to the right, it returns only the first match, and it can break if columns are inserted or deleted from the table.
18. How does INDEX-MATCH differ from VLOOKUP?
INDEX-MATCH is a combination of two functions: MATCH finds the position of a lookup value in a row or column, and INDEX returns the value at a specified row-column intersection. Unlike VLOOKUP, INDEX-MATCH can look up values in any direction (left or right), is not affected by column insertions or deletions, and generally performs faster on large datasets.
19. What does HLOOKUP do, and when would you use it?
HLOOKUP searches for a value in the first row of a table and returns a value from a specified row below it, using the syntax =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). It is useful when your data is arranged horizontally with headers in the top row and corresponding data in rows below, though INDEX-MATCH is often preferred for its greater flexibility.
20. How do text functions like LEFT, RIGHT, MID, LEN, TRIM, and CONCATENATE work?
LEFT, RIGHT, and MID extract characters from a text string: LEFT from the beginning, RIGHT from the end, and MID from a specified starting position. LEN returns the total number of characters in a string, TRIM removes extra spaces, and CONCATENATE (or the ampersand operator &) joins multiple text strings together. In newer versions of Excel, TEXTJOIN is preferred over CONCATENATE because it supports a delimiter and can ignore empty values.
Data Analysis & Pivot Tables
21. What is a PivotTable in Excel?
A PivotTable is an interactive tool that allows you to summarize, analyze, group, and reorganize large amounts of data without altering the source data. It lets you drag and drop fields into rows, columns, values, and filters areas to quickly create different views and summaries of your dataset.
22. How do you create a PivotTable in Excel?
To create a PivotTable, select any cell in your data range, go to Insert > PivotTable, confirm the data range and choose where to place the PivotTable (new or existing worksheet), then click OK. You then drag fields from the field list into the Rows, Columns, Values, and Filters areas to build your summary.
23. What are slicers in a PivotTable?
Slicers are visual filter controls that you can add to a PivotTable to quickly and intuitively filter data by clicking buttons instead of using dropdown menus. You can insert a slicer by selecting the PivotTable, going to PivotTable Analyze > Insert Slicer, and choosing the field you want to filter by. Slicers can also be connected to multiple PivotTables for synchronized filtering.
24. How do you group data in a PivotTable?
You can group data in a PivotTable by right-clicking on a row or column label and selecting Group. For date fields, Excel offers automatic grouping by days, months, quarters, or years. For numeric fields, you can specify a starting value, ending value, and interval to create custom groupings.
25. What is a calculated field in a PivotTable?
A calculated field is a custom field you create within a PivotTable that performs calculations using other fields in the PivotTable. You add one by going to PivotTable Analyze > Fields, Items & Sets > Calculated Field, then defining a formula such as =Revenue - Cost to create a Profit field.
26. How do sorting and filtering work in Excel?
Sorting rearranges data in ascending or descending order based on the values in one or more columns, accessible via Data > Sort or the A-Z/Z-A buttons. Filtering uses AutoFilter dropdowns (Ctrl+Shift+L) in column headers to display only rows that meet specified criteria, allowing you to hide irrelevant data temporarily.
27. What is Advanced Filter and how does it differ from AutoFilter?
Advanced Filter allows you to filter data using complex criteria defined in a separate criteria range on the worksheet, supporting AND/OR conditions across multiple columns and formula-based criteria. Unlike AutoFilter, Advanced Filter can copy filtered results to a different location and supports more sophisticated filtering logic.
28. How do you remove duplicate values in Excel?
You can remove duplicates by selecting the data range and going to Data > Remove Duplicates, then choosing which columns to check for duplicate values. Excel will delete entire rows where all selected column values match, keeping only the first occurrence. It is good practice to make a backup of your data before removing duplicates.
29. What is Data Validation in Excel?
Data Validation is a feature that restricts the type of data or values that can be entered into a cell, helping ensure data integrity. You set it up via Data > Data Validation, where you can define rules such as whole numbers within a range, dates, list selections, or custom formulas, and optionally provide input messages and error alerts.
30. What is What-If Analysis in Excel, and what tools does it include?
What-If Analysis is a set of tools for exploring different scenarios and outcomes by changing input values in formulas. It includes Goal Seek (finds the input value needed to achieve a desired result), Scenario Manager (stores and compares multiple sets of input values), and Data Tables (shows how changing one or two variables affects formula results), all accessible from the Data tab.
Charts & Data Visualization
31. What are the most common chart types in Excel?
The most common chart types include bar charts (horizontal comparisons), column charts (vertical comparisons), line charts (trends over time), pie charts (proportional composition), scatter plots (relationships between two variables), and combo charts (combining two chart types on one axis). The best chart type depends on the nature of your data and the message you want to convey.
32. How do you create a chart in Excel?
To create a chart, select the data range including headers, go to Insert, and choose the desired chart type from the Charts group. Excel will generate a chart based on your data, which you can then customize by changing the chart type, layout, colors, and other elements. You can also press Alt+F1 to insert a default chart instantly.
33. What are the main elements of a chart in Excel?
The main chart elements include the chart title, axis titles (horizontal and vertical), legend (identifies data series), data labels (display values on data points), gridlines (reference lines across the plot area), and the plot area itself. You can add, remove, or modify these elements using the Chart Elements button (+) that appears next to the chart or through the Chart Design and Format tabs.
34. What are sparklines in Excel?
Sparklines are tiny charts that fit inside a single cell, providing a compact visual representation of data trends. There are three types: Line, Column, and Win/Loss. You create them by going to Insert > Sparklines, selecting the data range and the location cell, and they automatically scale to represent your data within the cell.
35. How does conditional formatting work in Excel?
Conditional formatting changes the appearance of cells based on their values or specific conditions, making patterns and outliers easy to identify. You can apply it via Home > Conditional Formatting with options such as highlight cell rules, top/bottom rules, color scales, data bars, and icon sets. Custom rules using formulas are also supported for more complex scenarios.
36. What are color scales, data bars, and icon sets in conditional formatting?
Color scales apply a gradient of two or three colors across a range of cells based on their values, making it easy to spot high and low values. Data bars add horizontal bars inside cells proportional to the cell value, providing a quick visual comparison. Icon sets display icons (arrows, shapes, flags) based on value thresholds to categorize data visually.
37. How do you format and customize a chart in Excel?
You can format a chart by double-clicking on any chart element to open the Format pane, where you can change colors, fonts, borders, and effects. The Chart Design tab lets you change the chart type, switch row and column data, add chart elements, and apply pre-built chart styles. Right-clicking chart elements also provides quick formatting options.
38. What is a combo chart and when would you use one?
A combo chart combines two or more chart types (such as a column chart and a line chart) in a single chart, often using a secondary axis. It is useful when you need to compare data series with different scales or units, for example plotting revenue as columns and profit margin as a line on the same chart.
39. How do you create a dynamic chart in Excel?
A dynamic chart automatically updates when the underlying data changes, and it can be created by basing the chart on an Excel Table (Ctrl+T) or by using dynamic named ranges with OFFSET or INDEX formulas. When new data is added to a Table, the chart range expands automatically. Slicers and dropdown lists connected to the data can also add interactivity to charts.
40. What are chart templates in Excel and how do you use them?
Chart templates allow you to save a customized chart's formatting, layout, and style so you can reuse it for future charts without repeating the formatting work. You save a template by right-clicking a chart and selecting Save as Template, which creates a .crtx file. To apply a saved template, go to Insert > Charts > Templates and select your saved template.
Advanced Features
41. What is a macro in Excel?
A macro is a recorded sequence of actions or a set of VBA (Visual Basic for Applications) instructions that automate repetitive tasks in Excel. You can record a macro using the Developer tab > Record Macro, perform your actions, and stop recording. The macro can then be replayed to repeat those exact steps with a single click or keyboard shortcut.
42. What is VBA in Excel, and how is it related to macros?
VBA (Visual Basic for Applications) is the programming language built into Excel that allows you to write custom code to automate tasks, create user forms, and extend Excel's functionality beyond what is possible with recorded macros. While recorded macros generate VBA code automatically, writing VBA directly gives you far more control, including loops, conditional logic, and error handling.
43. How do you protect a worksheet and a workbook in Excel?
To protect a worksheet, go to Review > Protect Sheet, set a password, and choose which actions users can still perform (such as selecting cells or formatting). To protect a workbook, go to Review > Protect Workbook, which prevents users from adding, deleting, renaming, or reordering sheets. These protections are not encryption-grade security and should not be relied upon for highly sensitive data.
44. How are named ranges used in formulas?
Named ranges assign a meaningful name to a cell or range (e.g., naming B2:B100 as "Revenue"), which can then be used in formulas like =SUM(Revenue) instead of =SUM(B2:B100). This makes formulas easier to read, understand, and maintain, especially in complex workbooks. Named ranges also make it easier to navigate large workbooks and are used in data validation lists.
45. What are data tables in Excel's What-If Analysis?
Data tables are a What-If Analysis tool that shows how changing one or two input variables in a formula affects the result, displaying all outcomes in a structured table. A one-variable data table changes one input along a row or column, while a two-variable data table changes two inputs simultaneously. You create them by setting up your input values and formula, then going to Data > What-If Analysis > Data Table.
46. What is Power Query in Excel?
Power Query (also called Get & Transform) is a data connection and transformation tool that allows you to import data from various sources (databases, web pages, files, APIs), clean and reshape it, and load it into Excel. It uses a step-by-step visual interface with an underlying M language, and transformations are reusable -- refreshing the query re-applies all steps to updated source data.
47. What is Power Pivot in Excel?
Power Pivot is an Excel add-in that enables advanced data modeling and analysis by allowing you to import millions of rows of data from multiple sources, create relationships between tables, and write DAX (Data Analysis Expressions) formulas for calculated columns and measures. It extends the capabilities of regular PivotTables by supporting larger datasets and more complex calculations than standard Excel formulas.
48. What collaboration features does Excel offer?
Excel supports real-time co-authoring when files are saved to OneDrive or SharePoint, allowing multiple users to edit simultaneously. Other collaboration features include comments and threaded replies for discussion, Track Changes (in older versions) or the Activity pane for reviewing edits, sharing options with customizable permissions, and the ability to protect specific ranges for different users.
49. What are the differences between xlsx, xlsm, xlsb, and csv file formats?
XLSX is the default XML-based Excel format that does not support macros, while XLSM is the macro-enabled version that can store VBA code. XLSB is a binary format that results in smaller file sizes and faster performance for large workbooks. CSV (Comma-Separated Values) is a plain text format that stores only data without formulas, formatting, or multiple sheets, making it universally compatible across applications.
50. How do you import and export data in Excel?
To import data, you can use Data > Get Data (Power Query) to connect to external sources such as databases, text files, web pages, or other workbooks, and transform the data before loading it into Excel. To export data, you can use Save As to convert to formats like CSV, PDF, or XML, or copy data directly to other applications. For automated data refresh, Power Query connections can be set to update on a schedule or on file open.