1 / 27

MICROSOFT EXCEL TRAINING

MICROSOFT EXCEL TRAINING. Advanced & Business Data Analysis focused. Data Manipulation. Data Types. Data type, in Excel, refers to the characteristics of the information stored in the worksheet cell. There are 4, namely Number Text Boolean Formula. Data Formatting.

annot
Download Presentation

MICROSOFT EXCEL TRAINING

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MICROSOFT EXCEL TRAINING Advanced & Business Data Analysis focused info@urBizEdge.com 234-808-938-2423

  2. Data Manipulation info@urBizEdge.com 234-808-938-2423

  3. Data Types Data type, in Excel, refers to the characteristics of the information stored in the worksheet cell. There are 4, namely • Number • Text • Boolean • Formula info@urBizEdge.com 234-808-938-2423

  4. Data Formatting Excel allows data (mostly numbers) to be displayed in special formats. • Date and Time • Currency • Percentage & Fraction • Scientific • Custom Formats info@urBizEdge.com 234-808-938-2423

  5. Data Consistency To allow for scalability and reduce error tendencies, data consistency must be maintained. • Consistent formatting across a data field • Use of a compact data entry design • Consistent naming/spelling of static text entries • Use of descriptive and consistent sheet and range names info@urBizEdge.com 234-808-938-2423

  6. Planning for Scalability • Always work with the end in mind • Use a pre-determined naming scheme of files, worksheets and data fields • Name every sheet and file from the start • Avoid leaving formulas in cells except when necessary • Don’t have more than one table on the same column range, except you are making a summary table (or for charts) info@urBizEdge.com 234-808-938-2423

  7. Data Sorting Excel enables you to arrange data in a table in any order you want. • Ascending order • Descending order • By Color • By Value • Across Row or Column info@urBizEdge.com 234-808-938-2423

  8. Data Filtering Excel allows you to hide (or filter out) data with field entries you are not interested in. • Filter enables you to make dynamic charts when used in a summary table • Filter allows for arithmetic operation on the non-hidden data info@urBizEdge.com 234-808-938-2423

  9. Data Cleaning • Removal of Duplicate entries • Text-to-column to break a field to multiple fields • Grouping to make reports less clumsy • Data Validation to prevent errors • Conditional Formatting to identify unwanted entries info@urBizEdge.com 234-808-938-2423

  10. Charts info@urBizEdge.com 234-808-938-2423

  11. Chart Types • Line Chart • Column Chart • Bar Chart • Pie Chart • Other Charts • Sparklines info@urBizEdge.com 234-808-938-2423

  12. Secondary Charts Excel allows you to combine 2 or more charts on the same graph. • When to do this • How to do this • Using the same horizontal axis but two different vertical axes info@urBizEdge.com 234-808-938-2423

  13. Best Practices • Don’t use a 3D chart for business reporting • Use line charts to show trend over a period of time • Use Column charts to show performance of independent metrics • Use Bar charts when column charts will be too clumsy in display • Rarely use pie charts info@urBizEdge.com 234-808-938-2423

  14. Pivot Table & Chart info@urBizEdge.com 234-808-938-2423

  15. Pivot Table Pivot Table allows you to do instant analysis on a large data table. • Default Pivot Table • Different Fields • Applying Filter • Changing field values • Tabular Pivot Table • Using Formulas on Pivot table values • Pivot Table Tricks info@urBizEdge.com 234-808-938-2423

  16. Pivot Chart & PowerPivot Pivot chart is simply a chart of the pivot table. • The unique features of the Pivot Chart PowerPivot is an addin for Excel 2010, and built into Excel 2013 • It allows you to work with more external database and also on very large database info@urBizEdge.com 234-808-938-2423

  17. Business Data Analysis info@urBizEdge.com 234-808-938-2423

  18. Starting with the Worksheets • Naming Sheets • Moving and Copying sheets • Inserting Sheets and Tab Color • Linking Sheets in formula, special tips • Freezing Panes and Splitting Windows • Different Views – Normal, Page Layout and Page Break Preview info@urBizEdge.com 234-808-938-2423

  19. Lookup Functions • Vlookup • Hlookup • Looking up the last data in a row or column • Overcoming the limitations of Vlookup and Hlookup using Index and Match • Creative use of Lookup info@urBizEdge.com 234-808-938-2423

  20. Power Functions • IF & IFERROR • AND, OR • Text functions and special tricks • COUNTIFS, SUMIFS and AVERAGEIFS • Using the formula tab creatively • Formula auto-complete • Formula Evaluation info@urBizEdge.com 234-808-938-2423

  21. Reports & Dashboards info@urBizEdge.com 234-808-938-2423

  22. Professional Reports • Best Practices • Having an Almighty sheet • Knowing what to send and what to keep • Having the report audience in mind • E-mails: Done right the Excel way • Excel reports and printing • Excel Dashboard info@urBizEdge.com 234-808-938-2423

  23. Excel to PowerPoint info@urBizEdge.com 234-808-938-2423

  24. Excel to PowerPoint • Linking Charts in PowerPoint to Excel • Embedding Excel sheets in PowerPoint • Formatting Tables in PowerPoint • Formatting Charts in PowerPoint • PowerPoint tips info@urBizEdge.com 234-808-938-2423

  25. Excel VBA info@urBizEdge.com 234-808-938-2423

  26. Excel VBA • Why you’ll someday need VBA • Recording Macros • The Limitations of recorded Macros • The Easy way to writing your VBA program • Getting vital help • Essential tips • Examples info@urBizEdge.com 234-808-938-2423

  27. THANK YOU! E-mail: info@urBizEdge.com Call: 234-808-938-2423

More Related