Have a question?
Send enquiry
Message sent Close

Advanced Excel

What is Microsoft Excel?

Microsoft Excel is one of the most widely used spreadsheet programs in the world, and for good reason. It is a powerful tool for data analysis and visualization, and its capabilities have only increased with the introduction of advanced features in recent versions. In this blog post, we’ll explore some of the advanced Excel techniques and functions that can help you take your data analysis to the next level.

Contents/Functions of Microsoft Excel.

  1. Pivot Tables: Pivot tables are a great way to summarize and analyze large amounts of data. They allow you to easily pivot your data, change the row and column headers, and calculate sums, averages, and other aggregations. Pivot tables are especially useful when working with large datasets, as they can help you quickly identify trends and patterns in the data.
  2. Conditional Formatting: Conditional formatting is a feature that allows you to format cells based on their values. For example, you can use conditional formatting to highlight cells that contain values that meet certain criteria, such as those above or below a certain threshold. This can be a useful tool for quickly identifying trends or outliers in your data.
  3. Lookup Functions: Lookup functions are a type of Excel function that allow you to search for data in a table or range and return a corresponding value. There are several lookup functions in Excel, including VLOOKUP, HLOOKUP, INDEX, and MATCH. These functions are particularly useful when working with large datasets and can save you a lot of time and effort.
  4. Charts and Graphs: Charts and graphs are a powerful way to visualize your data and communicate insights to others. Excel provides a variety of chart types, including bar charts, line charts, pie charts, and scatter plots, among others. You can customize the appearance of your charts and graphs to make them visually appealing and effectively communicate your data.
  5. Macros: Macros are a feature in Excel that allow you to automate repetitive tasks. For example, you can create a macro to format a worksheet, insert a specific chart, or perform a complex calculation. Macros can be a great time-saver and can help you streamline your work in Excel.
  6. Power Pivot: Power Pivot is an add-in for Excel that provides advanced data modeling and analysis capabilities. It allows you to work with large amounts of data, perform complex calculations, and create powerful pivot tables and charts. Power Pivot is a great tool for data analysts and business intelligence professionals who need to work with large amounts of data.
  7. Power Query: Power Query is another add-in for Excel that provides advanced data retrieval and transformation capabilities. It allows you to easily connect to data sources, import and transform data, and load it into your Excel workbook. Power Query can save you a lot of time and effort when working with data, and it is a great tool for data professionals who need to work with large and complex datasets.

Conclusion

In conclusion, Excel is a powerful tool for data analysis and visualization, and its advanced features can help you take your data analysis to the next level. Whether you’re a data analyst, business intelligence professional, or simply someone who needs to work with data, mastering these advanced Excel techniques can greatly improve your productivity and efficiency.Thanks to our partners, you can find ties online to suit every preference and budget, from budget to top-of-the-range super stylish models.

Advanced Excel

What is Advanced Excel

  • Microsoft Excel is a leading and very high-demand office automation tool used all across industries, for example, Banks, Insurance, Telecom, Public Sector and many Small and Medium-size Organization to perform Complex calculations, Data Analysis, Reporting and Dashboards creation, understanding financial modeling, learning Advanced Excel is a highly valuable career Asset, a must-have criteria to qualify for many jobs!
  • Course participants will learn all the key Excel functions and features using Advanced Excel tools from basic to the mastery level of excel expertise, after completing the course participants would be able to perform their day-to-day job in more productive and professional way to improve organizational performance.

Who should attend?

  1. Professionals working in the field of accounting and financial reporting.
  2. CMAs, CPAs, ACCAs, Masters and Bachelors in Accounting and finance.
  3. Accountants
  4. Auditors                     
  5. Financial Analysts
  6. Portfolio Managers
  7. Management Accountants           
  8. Directors of Finance

 

Learning Objectives

  1. Take your Excel proficiency form Basic to Advance.
  2. Become more efficient in data analysis and reporting.
  3. Apply complex Excel functions and tools confidently.
  4. Better able to solve business problems using Excel

 

Agenda

Section 1

Excel: Advanced Formatting

  1. Using special number formats
    b. Built-in and custom formats Zero values
    c. Custom number formats
    d. Using functions to format text
    e. The PROPER, UPPER, and LOWER functions
    f. The SUBSTITUTE functions
    g. Working with styles
    h. Working with themes
    i. Theme colors
    j. Changing the orientation of cell contents
    k. Splitting merged cells
    l. Transposing data
    m. Using Paste Special operations
    n. Adding watermarks

Section 2

Excel: Range Names

  1. Defining names for cells or ranges
    b. Using a named range as a reference in a formula
    c. The Create from Selection command
    d. The Apply Names commands. The Name Manager

Section 3

Excel:  Functions

  1. Excel: Logical and Statistical Functions
    i. The IF function
    ii. The OR, AND, and NOT functions
    iii. Nested IF functions
    iv. The IFERROR function Math and statistical functions
    v. The SUMIF function
    vi. The COUNTIF function
    vii. The AVERAGEIF function
    viii. The ROUND function
    b. Excel: Financial and Date Functions
    i. The PMT function
    ii. TODAY, YEAR, DAYS, YEARFRAC
    iii. NETWORKDAYS
    iv. NOW
    v. Subtracting times
    vi. Creating array formulas
    vii. Applying arrays to functions
    viii. Modifying array formulas
    ix. Viewing/Hiding formulas in a worksheet
    x. Disabling automatic calculation of formulas
    c. Excel: Lookups
    i. Using VLOOKUP to find exact matches
    ii. Using VLOOKUP to find approximate matches
    iii. Using HLOOKUP to find exact matches
    iv. Using HLOOKUP to find approximate matches
    v. Using MATCH and INDEX

Section 4

Excel: Data Validation and Conditional Formatting

  1. Validating data
    Setting data validation rules
    c. Pasting validation rules
    d. Using date and list criteria in data validation rules
    e. Conditional Formatting Using a formula to apply conditional formatting

Section 5

Excel: Advanced Charts

  1. Chart formatting options
    Changing a chart’s scale
    c. Formatting data points
    d. Formatting pie charts
    e. Combination charts
    f. Adding trendlines
    g. Sparklines
    h. Chart templates
    i. Adding pictures

Section 6

Excel: Pivot Tables

  1. The PivotTable command
    b. Using an external data source
    c. Adding fields
    d. Using fields to filter data
    e. Inserting slicers
    f. Modifying slicers
    g. Deleting slicers
    h. Rearranging PivotTables
    i. Moving fields
    j. Collapsing and expanding details
    k. Refreshing PivotTable data
    l. Formatting PivotTables
    m. Using styles
    n. Changing field setting

Section 7

Excel: Data Analysis Tools

  1. Goal Seek
    b. Scenario Manager
    c. Solver
  2. Excel: Managing Large Workbooks
  3. Viewing large worksheets
    b. Locking row and column headings in place
    c. Opening and arranging windows
    d. Splitting a worksheet
    e. Hiding rows and columns
    f. Hiding and unhiding worksheets
    g. Minimizing the Ribbon to see more of the worksheet
    h. Printing large worksheets
    i. Print titles
    j. Page breaks
    k. Page Break Preview
    l. Using different even and odd headers and footers
  4. Printing multiple worksheets