by Crispo Mwangi | May 18, 2022 | ADVANCED EXCEL, CHARTS
Waterfall charts are used to explain the +ve and -ve components of a change in value between two points. It reveals the contribution per factor affecting the item being analyzed. For example, If you had Budgeted $ 66M but ended up spending $ 72M, you can easily tell...
by Crispo Mwangi | May 13, 2022 | INTERMEDIATE EXCEL
Accurate data entry saves tonnes of time in data clean up & analysis. Below is how to Allow Only UPPERCASE, lowercase, Or Proper case Entries in Excel. How it Works The EXACT Function compares two text strings and returns TRUE if they are the same else FALSE. This...
by Crispo Mwangi | May 4, 2022 | ADVANCED EXCEL
Summing data based on weekdays or weekends is easy, but what if you are required to SUMIF Excluding Weekends & Holidays? There are 2 ways of doing this: Using SUM, IF & WORKDAY FUNCTION =SUM(IF ( WORKDAY(tbls[Sales Date]-1,1,Holidays) = tbls[Sales...
by Crispo Mwangi | Apr 28, 2022 | ADVANCED EXCEL
Having a dynamic training calendar is a MUST for all HR professionals. How it Works: Type the below formula on the Calendar sheet. =IFERROR( TEXT( INDEX(trainings[[Date From]:[Date From]], MATCH(TRUE,(NOT(ISBLANK(trainings[[Date From]:[Date...
by Crispo Mwangi | Apr 11, 2022 | INTERMEDIATE EXCEL
By default, the SUMIFS function is not case-sensitive. To make it case-sensitive and do an exact match, follow the below steps: SUMIFS COMPLETE EXACT MATCH Alternative 1: Using a combination of SUM, IF & EXACT We shall start by doing a complete exact match then a...
Recent Comments