Advance MS Excel 2007 Training

click on this link for word document for training notes

http://www.scribd.com/doc/167873403/Advance-MS-Excel-2007-Training-Engineering-Design-Application

Contents

Terminology Used

Task 1: Displaying formula

F2 to display formula

To display all function for complete sheet- Cont+ ` (Button above tab)

Task 2: using function SUM MAX, MIN, AVERAGE, COUNT

Sum Function

( Alt+ = ) for sum

 Max

=Max (select the range)

MIN(Select The range)

 

 

COUNT(Select the range)

 

AVERAGE(Select the range)

COUNT(Select the range)

Note Selection- select top cell & choose scroll down key or (just click shift + last cell)

Task 3 - Defining naming of rangeTask 4– Use of COUNT, COUNTA & COUNT BLANKTask 5: Sumif FunctionTask 6: Test FunctionTask 7: Date & time functionTask 8: DATE DIFFERENCE- DATE OF BIRTH exect ageTask 9: SortingTask 10: Text to columnTask 11: Conditional FormattingTask 12: Excel ValidationTask 13 : CELL REFERENCINGTask 14: IF FunctionTask 14: AND & OR & Rank FunctionTask 15: VlookupTask 16:  match & index functionTask 17- ChartingTask 18 Trend line for forecasting sales or prediction about future data behavior.Task 19 Combination chart & two axis.- if there is big gap in data range to represent properly.Task 20 Pivot TableTask 21 % AnalysisTask 22- Drill down – means creating separate sheet for  each dept( for example)Task 23 GroupingTask 24   Creating separate pivot table as per resignTask 25 – Pivot ChartTask 26 Formula in Pivot tableTask 27  Stratify the order amount in ranges of 0-5000, 5000-10000 and so on.Task 28 Protection of Entire sheetTask 29 Cell level protectionTask 30 - Data ConsolidationTask 31 - Hyperlink

Advanced Microsoft Excel refers to the use of Excel's more advanced features and functions to perform complex data analysis, modeling, and reporting tasks. Here are some key topics and features that fall under the category of advanced Excel skills:

1. PivotTables and PivotCharts: Creating PivotTables to summarize and analyze large datasets, and using PivotCharts to visualize data trends and patterns.

2. Advanced Formulas and Functions:

   • VLOOKUP and HLOOKUP: Searching for and retrieving data from tables.
   • INDEX and MATCH: More versatile alternatives to VLOOKUP and HLOOKUP.
   • SUMIF, COUNTIF, AVERAGEIF: Performing calculations based on specific conditions.
   • SUMIFS, COUNTIFS, AVERAGEIFS: Applying multiple criteria for calculations.
   • IF, AND, OR: Building complex logical tests and calculations.
   • Nested Functions: Combining functions within functions for advanced calculations.

3. Data Validation: Applying data validation rules to ensure data accuracy and consistency in input.

4. Conditional Formatting: Applying formatting to cells based on certain conditions, making data visualization more dynamic.

5. Advanced Charting: Creating complex charts like combination charts, trendlines, and dual-axis charts to effectively represent data.

6. Data Analysis Tools:

   • Data Tables: Performing sensitivity analysis for different variables.
   • Scenario Manager: Managing multiple scenarios for decision-making.
   • Solver Add-In: Optimizing solutions based on constraints and goals.
   • Goal Seek: Determining input values needed to achieve a desired outcome.

7. Data Consolidation: Consolidating data from multiple sources using tools like Consolidate and Power Query.

8. Advanced Filtering and Sorting: Applying multiple criteria for filtering and sorting data.

9. Array Formulas: Using array formulas to perform calculations across multiple cells at once.

10. Data Validation: Setting up rules to validate data entry and prevent errors.

11. Macros and VBA: Creating and editing macros to automate repetitive tasks, using the Visual Basic for Applications (VBA) programming language.

12. Power Query and Power Pivot: Using these powerful tools for data transformation and creating advanced data models within Excel.

13. Dynamic Named Ranges: Creating named ranges that automatically adjust as data changes.

14. Consolidation and Grouping: Consolidating data from multiple sheets or ranges and grouping data for summaries.

15. What-If Analysis: Exploring different scenarios by changing variables and observing the impact on outcomes.

16. Keyboard Shortcuts: Utilizing keyboard shortcuts to streamline tasks and increase efficiency.

17. Protecting and Securing Workbooks: Applying password protection, restricting editing, and setting permissions.