Course «Excel - Expert level» in the computer training center «Basic»

COURSE PROGRAM

Topic 1. Interface overview, data entry and formatting

• Principles of data storage in Excel.
• Custom formats.
• Effective techniques and tricks when working in Microsoft Excel.

Topic 2. Methods for fast filling with data. Notes. Hyperlinks

• Copying, moving cells of the workbook.
• Paste special (transpose, value, picture, etc.) Use paste special to calculate without formulas.
• Find and replace, selection of groups of cells.
• Autocompletion, using a drop-down list to select values.
• Autocomplete, progressions.
• Lists, creating custom lists.
• Cell notes (insert, edit, display on screen, print).
• Working with hyperlinks (hyperlinks to files, web page and place in the document).

Topic 3. Conditional formatting

• Standard conditional formatting functionality. Highlighting duplicate values in a table.
• Using visualization to display in cells histograms, various icons, and color scales.
• Conditional Formatting Rules Manager. Editing formatting rules.
• Using formulas and functions to create conditional formatting rules.
• Find cells with conditional formatting.

Topic 4. Working with sheets in a workbook

• Organization of the workbook. Working with sheets: renaming, pasting, moving and copying, deleting.
• Sheet structure. Simultaneous data entry on multiple sheets.

Topic  5. Data protection in Microsoft Excel

• Setting a password to open the book.
• Hiding formulas.
• Protection of the sheet from changes. Selective range protection.
• Protection of the structure of the book.

Topic 6. Calculations in Microsoft Excel

• Creation of elementary formulas.
• Copying formulas.
• Types of errors in formulas, correction of formulas.
• Relative, absolute, mixed links.
• Using data from other sheets in formulas - links removed. Three-dimensional formulas.
• Trace formulas. Influencing and dependent cells.

Topic 7. Functions

• Function wizard.
• AutoSum (SUM, AVERAGE, COUNT, MIN, MAX).
• Mathematical and statistical functions. Functions of summation by one and several criteria (SUMIF, SUMIFS). Counting functions based on one and several criteria (COUNTIF, COUNTIFS). Averaging functions (AVERAGEIF, AVERAGEIFS).
• Text functions (combining data - &, CONCATENATE. MID, DLSTR functions, bringing information into the correct form: TRIM).
• Date processing functions: calculation of experience, age, period of operation RAZDAT, calculation of the number of working days in the time period NETWORKDAYS.
• Logical functions (IF, OR, AND, IFERROR).
• Nested functions.

Topic 8. Working with diagrams

• Construction, editing of diagrams.
• Types and types of diagrams.
• Formatting charts.
• Typical charts (bar graphs, graphs, pie charts, etc.).
• Combo charts.
• Charts with 2 axes.
• Sparklines.

Topic 9. Output of a document to print. Page layout

• Convenient presentation of data on the screen. Freezing areas (rows, columns).
• Preparing a workbook for printing. Page Layout and Page Layout modes.
• Setting page parameters: setting margins, changing page orientation, printing through rows (columns), printing grid, headers. Page breaks. Print area.
• Printing a document. Print part of a table. Printing diagrams.

Topic 10. Filtering and sorting data

• Sorting data. Simple and multilevel data sorting. Sorted by color. Sorting options.
• Filtering data. Selection by value, by format, by condition. Numeric, text, date filters. Selection of the highest and lowest values. Filtering data by the content of the active cell.

Topic 11. Working with large tables. Ordering and summarizing data

• Subtotal tool.
• Grouping and structure. Column and row grouping. Working with control buttons. Multilevel grouping of information.
• Data Consolidation - Combining data from multiple tables into one.
• Removing duplicates.
• Column-by-column text. Text parser wizard.
• Auto-formatting tables. Smart Excel spreadsheet: creation, editing, formatting.

Topic 12. VLOOKUP (HLOOKUP) function. Array functions

• Using the VLOOKUP (HLOOKUP) function to search and substitute data.
• Suppression of # N / A errors in the absence of values.
• SUMPRODUCT function

Topic 13. Summary tables

• Creation of pivot tables.
• Apply styles to quickly format your report. Formatting numeric fields.
• Rotation of pivot tables.
• Setting up the fields of the pivot table.
• Create dynamic charts using pivot tables.
• Filtering data: filters, slicers.

Topic 14. Macros. Automating Tasks Using Macros

• Recording macros using a macro recorder.
• Creation of controls for running macros.
• Inserting the finished code into the file.
• Testing.