By the results of the block you will be able to prepare data for quick work and manage a table with 1000+ rows, learn to use complex tools and functions of Excel to solve business problems
and visualize results.
1. Introductory unit:
- Document and cell locking;
- The simplest functions;
- Hotkeys.
2. Data preparation:
- Data import;
- CLEAN, TRIM, SUBSTITUTE, VALUE, TEXT functions;
- LEN, CONCATENATE, SEARCH, RIGHT, LEFT functions;
- Removal of duplicates and empty lines.
3. Data retrieval:
- VLOOKUP and HLOOKUP functions;
- INDEX(MATCH) functions;
- OFFSET and INDIRECT functions;
- Search and replace data in Excel.
4. Logical Functions:
- Functions IF, IF + AND, IF + OR, IFS, ISBLANK;
- Nested Functions;
- ISNA, IFERROR error functions;
- COUNTIFS, SUMIFS functions.
5. Summary Tables:
- Construction of summary tables;
- Sorting and filtering;
- Slicers and visualization.
6. Visualization of results:
- Using conditional formatting;
- Data Validation;
- Charts and graphs in Excel;
- Formalizing the work in Excel.
7. Final case to consolidate the material in practice
Case example: you will act as an analyst in a cellular operator company. You must analyze the structure of cellular service usage among subscribers aged 18-25 and determine which tariff
terms will be most relevant to this audience.