Page 268 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

• Add calculated fields to perform custom calculations within the PivotTable.

• Use slicers to filter data and easily analyze subsets of information.
• Create Pivot Charts to visualize and explore data interactively.
• Refresh data automatically when the source data changes.
VLOOKUP and INDEX-MATCH Functions:
Master the use of VLOOKUP and INDEX-MATCH functions for advanced data retrieval and analysis. These
functions allow you to search for a value in one column and return a corresponding value from another column.
Some tips for using these functions effectively are:

• Use the INDEX-MATCH combination when the lookup value is not in the leftmost column of the table.
• Combine multiple criteria by nesting INDEX-MATCH functions.
• Utilize approximate match for finding values within a range.
Excel Tables:
Convert your data range into an Excel table to leverage advanced features and enhance data management.
Excel tables provide:
• Dynamic range references that automatically expand when new data is added.
• Automatic formatting and styles to make your data visually appealing.

• Easy sorting and filtering options for quick data analysis.
• Structured referencing in formulas for better readability and maintainability.
• Total rows that automatically calculate sums or other aggregations for each column.
Named Ranges:
Assign names to cell ranges to make formulas more readable and easier to manage. By using named ranges,
you can:
• Replace complex cell references with meaningful names in formulas.
• Improve formula transparency and reduce the risk of errors.

• Create more flexible and adaptable formulas that can refer to named ranges across worksheets or workbooks.
• Easily update formulas by modifying the named range instead of manually adjusting cell references.
Power Query:
Power Query is a powerful data transformation and cleaning tool available in newer versions of Excel. It allows
you to:
• Import and combine data from various sources, such as databases, CSV files, or websites.
• Perform data cleaning operations, such as removing duplicates, filtering rows, or splitting columns.
• Merge or append multiple datasets into a single table.
• Apply transformations and calculations to shape the data before loading it into Excel.

• Establish a connection to external data sources for automatic data refresh.
Macros:
Use macros to automate repetitive tasks in Excel. Macros are recorded sequences of actions that can be played
back with a single click or assigned to a keyboard shortcut. With macros, you can:
• Automate formatting tasks, such as applying specific styles, adjusting column widths, or adding headers and
footers.
• Perform data manipulations, such as sorting, filtering, or removing blank rows.

• Create custom functions or calculations that are
• not available in built-in Excel functions.

255

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77