Excel: The Complete Guide

Seminarziel

You can now handle Excel workbooks efficiently. You create professional reports effortlessly; you can import external data; you can split, filter and process data. The exercises can be used as a model for your own tasks. Spreadsheets test values input and warn if they seem incorrect.

For in-house Excel training, the topics covered can be adapted to meet your needs. Additional topics from VBA and Access can also be included. This course is also available in German.

Inhalt

• Excel user interface 
  • The ribbon
  • Quick Access Toolbar 
  • The backstage view
  • Functions bar and Name Box
  • Status bar with information areas and display controls
  • Workbook and spreadsheet structure
  • Context menus
• Using Excel Help
  • Help resources on Office.com
• Setting up the Software
  • Personalising Microsoft Excel 2013
  • Basic security settings in the Trust Centre
• Using Excel files
  • Creating a new blank workbook and using templates
  • Opening existing workbooks
  • Saving locations (web locations / computer) 
  • Saving workbooks in different file formats (XSLX, XLS, CSV, PDF)
  • Importance of other file formats and risks
  • File properties for self-organisation and teamwork
  • Opening old Excel files, compatibility mode and associated restrictions
• Workbook structure 
• Worksheets dimensions and coordinates 
  • Changing workbook views
  • Headers and footers 
  • Add, name, move, duplicate, and delete worksheets 
• Data entry
  • Entering text and figures
  • Correct, delete entries
  • Cell components: Content vs. format
  • Moving cells with content
• Cell formatting
  • Basic cell formatting
    • Fill and text colours
    • Number formats
    • Currency formats
    • Date and time formats
    • Text formats
    • Table borders
    • Alignment
    • Merge and centre cells
  • Cell format templates for simplified layout
  • Cell comments as memory aids 
• First calculations
  • Entering and correcting formulas
  • Formulas vs. functions
  • Relative and absolute cell references
• Calculations with functions
  • Sum 
  • The function wizard and function categories
  • The functions AVERAGE, COUNT, MAX, MIN
• Clipboard
  • Copy, cut, paste cells
  • Paste contents from the clipboard
    • Formulas
    • Values
    • Formats
    • Comments
• Create tables
  • Automatic text lists
  • Mathematical series
• Working on existing tables
  • Navigation in large tables
  • Inserting & deleting columns and rows
  • Splitting the screen / freezing rows and columns
  • Converting a range into a table for analysis and simplified formatting
• Basics of data analysis
  • Sorting individual columns and entire tables
  • Autofilter 
• Charting basics
  • Chart wizard: Excel recommends charts
  • Which chart is suitable for which data set
  • Quick layouts and chart format templates
  • Save chart templates
• Print preparation and printing
  • Put print preview button in the quick access toolbar
  • Page setup
  • Row and column breaks 
  • Print titles for multiple pages
  • Set print areas
  • Scaling the printed page
• Complex formulas and functions
  • Functions from the categories
    • Logical functions
    • Statistical functions
    • Financial mathematics
    • Date and time
  • Nesting several functions
  • Formula monitoring
• Large tables and folders
  • Outlining tables
    • Working with outline forms (automatic and manual)
    • Formatting outline levels 
    • Removing outlines 
    • Setting and optimising page breaks for clear layout
  • Protecting content
  • Conditional Formatting 
    • Rules with formulas
    • Use of symbol sets ("traffic light lists")
    • Editing, checking and deleting formatting rules
• Combining data mathematically (consolidation)
  • Consolidation of row and column values across several tables
  • Consolidation with and without links to source data
  • Consolidation based on headings
  • 3D references
• Forecasting
  • Working with data tables
  • Working with scenarios
  • Goalseek
• Checking data
  • Validation
  • Creating picklists
• Analysing data in dynamic tables
  • Sorting with sort commands, 
  • Sorting via the filter menu
  • Filter functions
  • Auto filters
  • Special filters with complex criteria
  • Set data output in special output area
  • Using the results row
  • Using statistical functions such as COUNTIF and SUMIF
• Partial results
  • Create, edit, remove
  • Multiple partial results with different functions
  • Partial results in filtered lists
  • Manual use of the partial result function
• Data links inside and outside Excel
  • Data transfer between spreadsheets
  • Linking files
  • Monitor, update, delete links
  • Data links with Word or PowerPoint
• Troubleshooting imported data
  • Flash preview
  • Text columns
  • Linking texts
• Excel security 
  • Workbook Security 
  • Worksheet Security 
  • Locking workbooks
  • Encrypt workbooks
  • Write-protect workbooks