15 Essential MS Excel Topics
to
Master for Productivity and Data Analysis
Here are 15 important topics in MS Excel that are essential for mastering the tool:
1. AutoFill In Excel
2. Cell References
o Relative, absolute, and mixed references
3. Formatting in Excel
o
Cell formatting (number, text, currency, date)
4. Formulas and Functions
o All formulas
o Basic Mathematical formulas
(SUM, AVERAGE,MIN,MAX, COUNT,COUNTA,COUNTIF,SUMIF,SUMIFS,ROUND,ROUNDUP,ROUNDDOWN,INT,MOD,PRODUCT ,POWER,SQRT,EXP,ABS,RAND)
o Logical functions
(IF, AND, OR,NOT,IFERROR ,IFNA, XOR,SWITCH, IFS,CHOOSE,TRUE,FALSE,ISNUMBER ,ISEVEN,ISODD,ISERR,ISERROR,ISBLANK,ISLOGICAL ,ISFORMULA )
o Text functions
(LEFT, RIGHT, CONCATENATE, TEXTJOIN,MID,LEN,TRIM,LOWER,UPPER,PROPER,REPT,SUBSTITUTE,REPLACE,FIND,SEARCH,TEXT,DOLLAR,VALUE,EXACT,CODE)
o Date and Time functions
(NOW,TODAY,YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,WEEKDAY,WEEKNUM,DATEDIF,EDATE,EOMONTH,DATE,TIME,YEARFRAC ,NETWORKDAYS ,WORKDAY ,ISOWEEKNUM ,DATEDIF)
o Essential Financial functions
(PMT,PV,FV,NPV,IRR,RATE,SLN,DB,DDB,CUMIPMT)
o Lookup and references functions
(VLOOKUP,HLOOKUP,INDEX,MATCH ,LOOKUP, CHOOSE,OFFSET, INDIRECT, TRANSPOSE, FORMULATEXT )
5. Sorting and Filtering
o
Sorting data by columns and criteria
o
Applying filters to narrow down data
6. Working with Tables
o
Creating and formatting Excel tables
o
Table features (structured references, total row)
7. Data Validation
o
Restricting input using dropdown lists, custom
rules
8. Charts and Graphs
o
Creating various chart types (bar, line, pie)
o
Formatting and customizing charts
9. PivotTables and PivotCharts
o
Summarizing large datasets
o
Creating dynamic charts
10. Data Analysis Tools
o
Using Goal Seek and Solver
o
What-If Analysis
11. Lookup and Reference Functions
o
VLOOKUP, HLOOKUP, and XLOOKUP
o
INDEX and MATCH
12. Working with Large Datasets
o
Freezing panes, splitting windows
o
Using advanced filters and removing duplicates
13.
Keyboard Shortcuts and Productivity Tips
o
Essential shortcuts for efficiency
o
Quick access toolbar customization
14.
Macros and Automation
o
Recording simple macros
o
Running and editing macros
15.
Protecting and Sharing Workbooks
o
Password-protecting sheets/workbooks
o
Sharing and collaborative editing
