Excel Intermediate Lesson 7: Logic With IFS, SWITCH, AND and OR
Replace long nested IFs with IFS and SWITCH, combine conditions with AND/OR, and count with AND/OR logic in COUNTIFS. Grades, commissions and follow-up flags.
Read article →Atul Vij
Excel Intermediate Lesson 6: Business Dates (WORKDAY, NETWORKDAYS, EOMONTH, FY Quarters)
Excel Intermediate Lesson 6: Business Dates (WORKDAY, NETWORKDAYS, EOMONTH, FY Quarters)
Due dates, delivery dates with holidays, working days in a month, Indian financial-year quarters and FY labels, all with formulas you can copy.
Read article →Excel Intermediate Lesson 5: Cleaning Text With TEXTSPLIT, TEXTBEFORE and TEXTAFTER
Excel Intermediate Lesson 5: Cleaning Text With TEXTSPLIT, TEXTBEFORE and TEXTAFTER
Clean messy ERP and bank exports: split pipe-separated text, fix case and spaces, pull invoice numbers and names with TEXTBEFORE, TEXTAFTER, TEXTSPLIT, TRIM and…
Read article →Excel Intermediate Lesson 4: Dynamic Arrays (FILTER, SORT, UNIQUE, SEQUENCE)
Excel Intermediate Lesson 4: Dynamic Arrays (FILTER, SORT, UNIQUE, SEQUENCE)
One formula, many results: FILTER, SORT, SORTBY, UNIQUE and SEQUENCE spill whole lists that update themselves. With real examples from a sales register.
Read article →Excel Intermediate Lesson 3: INDEX/MATCH and Advanced XLOOKUP
Excel Intermediate Lesson 3: INDEX/MATCH and Advanced XLOOKUP
Two-way lookups, lookups to the left, last-match searches and not-found defaults with INDEX/MATCH and XLOOKUP, with a regional targets grid.
Read article →Excel Intermediate Lesson 2: SUMIFS, COUNTIFS and AVERAGEIFS Reports
Excel Intermediate Lesson 2: SUMIFS, COUNTIFS and AVERAGEIFS Reports
Build region, month and status reports from a raw sales register with SUMIFS, COUNTIFS and AVERAGEIFS, including date ranges, wildcards and not-equal criteria.
Read article →Running Total and Running Count in Excel (Classic, Table and SCAN Methods)
Running Total and Running Count in Excel (Classic, Table and SCAN Methods)
Cumulative totals, running counts, running totals by category and month-to-date sums — with expanding ranges, SUMIFS, Excel Table formulas and the new SCAN function…
Read article →Excel Intermediate Lesson 1: Named Ranges and Structured References
Excel Intermediate Lesson 1: Named Ranges and Structured References
Stop writing Sales!K2:K81 everywhere. Name your ranges or turn data into an Excel Table so formulas read like =SUM(Amount) and grow with new rows.
Read article →Excel TEXT Function: 20 Format Codes You Will Actually Use
Excel TEXT Function: 20 Format Codes You Will Actually Use
The TEXT function turns numbers and dates into exactly the text you want: dd-mmm-yyyy, day names, leading zeros, lakh/crore commas, percentages and times. 20…
Read article →Power BI Intermediate Lesson 10: Project – Sales Performance vs Target
Power BI Intermediate Lesson 10: Project – Sales Performance vs Target
Final project: targets without a relationship via TREATAS, % of target with status, months above average, FYTD trend and an RLS-ready one-page report.
Read article →Power BI Intermediate Lesson 9: Drill-Through, Tooltip Pages and Bookmarks
Power BI Intermediate Lesson 9: Drill-Through, Tooltip Pages and Bookmarks
Make reports explorable: right-click drill-through to a customer page, report-page tooltips with a mini chart, and bookmarks with buttons to switch views.
Read article →Power BI Intermediate Lesson 8: Row-Level Security (Static and Dynamic)
Power BI Intermediate Lesson 8: Row-Level Security (Static and Dynamic)
Show each regional manager only their region's data: static roles, dynamic RLS with USERPRINCIPALNAME and a user table, and how to test with View…
Read article →