Blog Posts on MS Excel
You can pull a Google Sheet into Excel with Power Query and refresh it whenever the sheet changes: no download, no copy-paste. The trick is one small change to the sheet’s share link. Works inExcel 2016 and later, Microsoft 365 (Windows); Excel for Mac with Power Query NeedsA Google Sheet shared as “Anyone with the … Read more
To count how many dates of birth fall in July, compare the month of each date with 7: =SUMPRODUCT(–(MONTH(C2:C17)=7)). The same idea counts birthdays this month, or lists who they are. FormulaSUMPRODUCT + MONTH Excel 365FILTER to list the names Works inAll versions Count birthdays in a month =SUMPRODUCT(–(MONTH($C$2:$C$17)=7))Copy Step Result MONTH($C$2:$C$17) {12;7;11;3;1;8;…}, the month … Read more
To count how many dates fall in 2020, use COUNTIFS with a date range: =COUNTIFS(B3:B15,">="&DATE(2020,1,1),B3:B15,"<"&DATE(2021,1,1)). SUMPRODUCT with YEAR does the same in a shorter formula. FastCOUNTIFS with a date range ShortSUMPRODUCT + YEAR Every year at onceUNIQUE + COUNTIFS, or a PivotTable Method 1: COUNTIFS =COUNTIFS(B3:B15,">="&DATE(2020,1,1),B3:B15,"<"&DATE(2021,1,1))Copy Counts dates on or after 1 January 2020 and … Read more
To count how many cells in a range show an error, use =SUMPRODUCT(–ISERROR(E3:E12)). You can also count one error type, such as #N/A, or count only the cells that are not errors. All errorsSUMPRODUCT + ISERROR One error typeCOUNTIF Works inAll versions Count all errors =SUMPRODUCT(–ISERROR(E3:E12))Copy Step Result ISERROR(E3:E12) {FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE} — turns TRUE/FALSE into 1/0 … Read more
To count how many dates of birth fall in July, compare the month of each date with 7: =SUMPRODUCT(–(MONTH(C2:C17)=7)). The same idea counts birthdays this month, or lists who they are. FormulaSUMPRODUCT + MONTH Excel 365FILTER to list the names Works inAll versions Count birthdays in a month =SUMPRODUCT(–(MONTH($C$2:$C$17)=7))Copy Step Result MONTH($C$2:$C$17) {12;7;11;3;1;8;…}, the month … Read more
To split Anil Kumar into Anil and Kumar, use TEXTBEFORE and TEXTAFTER in Microsoft 365, or LEFT and FIND in any version. These formulas also handle middle names and people with only one name. First name (365)=TEXTBEFORE(A2," ",,,1) Last name (365)=TEXTAFTER(A2," ",-1,,,"") Any versionLEFT + FIND Excel 365: TEXTBEFORE and TEXTAFTER =TEXTBEFORE(A2," ",,,1)Copy TEXTBEFORE returns … Read more
The following is the generic formula to extract the first name from a full name. =LEFT(“Full Name”,SEARCH(” “,”Full Name”)-1) Explanation SEARCH function in the formula will return the position of ” ” (Space character) from the Full name. In the formula used in the cell C3, SEARCH function will return the position of ” ” … Read more
To show the current sheet’s name in a cell, for a report title or a header, use CELL(“filename”) and take everything after the closing bracket. In Excel 365 it’s a short TEXTAFTER formula. Excel 365TEXTAFTER + CELL Any versionMID + FIND + CELL NeedsThe workbook to be saved Excel 365 =TEXTAFTER(CELL("filename",A1),"]")Copy Any version =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)Copy 31 … Read more
Instead of adding VLOOKUP columns to bring region or category into a sales table, connect the tables with a relationship. A PivotTable can then use fields from all of them at once, with no extra formulas. WhereData → Relationships PivotTableUse this workbook's Data Model Works inExcel 2013 and later for Windows Video The problem Sales_Data … Read more
A User Defined Function (UDF) which can split data into multiple Rows or Columns on the basis of the specified delimiter in Excel is explained in this blog post. Watch the video on Excel Formula to split data into Columns or Rows Text to Columns feature is the most commonly used Tool to split data … Read more