Convert PDF to Excel Using Power Query

Convert PDF to Excel

Excel’s From PDF connector pulls tables out of a PDF into Power Query, where you can clean them and load them to sheets. Replace the PDF with a newer one and click Refresh to update. WhereData → Get Data → From File → From PDF Works inMicrosoft 365 and Excel 2021 for Windows Not forScanned … Read more

Get the Decimal Part of a Number in Excel

Get the Decimal Part

To get 0.159 from 4.159, subtract the whole-number part: =B3-TRUNC(B3). MOD and Power Query work too, but they handle negative numbers differently, so pick the method that gives the answer you need. Formula=B3-TRUNC(B3) Always positive=MOD(ABS(B3),1) Whole part=TRUNC(B3) Watch the short video: Excel formula to remove digits after decimal point Formulas compared Formula 4.159 −4.159 =B3-TRUNC(B3) … Read more

Extract Numbers in Excel: From a Column or From Text

Extract Numbers From Text

Two different jobs share this name: pulling the number cells out of a mixed column, and pulling the digits out of text like INV-2041A. Here are formulas and tools for both. Number cellsFILTER + ISNUMBER, Go To Special, Power Query Digits in textTEXTJOIN + MID, REGEXEXTRACT Works inAll versions (dynamic formulas: 365) Watch the short … Read more

Reverse Text in Excel (4 Ways)

Reverse Text in Excel

To turn Hana into anaH, Excel 365 can do it with one formula. In older versions, use a one-line VBA function, or Power Query’s Text.Reverse. Excel 365CONCAT + MID + SEQUENCE Any versionStrReverse in VBA Power QueryText.Reverse Watch the short video: Excel formula to Reverse a Text or Number 1. Formula (Excel 365) =CONCAT(MID(B3,SEQUENCE(LEN(B3),,LEN(B3),-1),1))Copy Step … Read more

Count Each Item in a List in Excel (4 Ways)

Count Each Item in a List

To see how many times each item appears, use UNIQUE and COUNTIF in Excel 365, GROUPBY for a sorted summary, a PivotTable, or Power Query’s Group By. All four update when the list changes. Excel 365UNIQUE + COUNTIF, or GROUPBY Any versionPivotTable RefreshablePower Query Group By The list The list is an Excel Table named … Read more

Find Common Values Between Two Lists in Excel

Find Common Values

To find the names, codes or items that appear in both lists, test each value with COUNTIF, pull out the matches with FILTER, or highlight them with conditional formatting. Power Query’s inner join does the same for large tables. Watch the short video: Excel formula to find the Common Values between two Lists fx=FILTER(B3:B10,COUNTIF(D3:D9,B3:B10)) ProblemList … Read more

Sum of the First 50 Natural Numbers in Excel (5 Ways)

Sum of 1 to 50 in Excel

1 + 2 + 3 + … + 50 = 1,275. It's a classic puzzle, and a neat way to compare Excel tools: a one-line maths formula, SEQUENCE, SUMPRODUCT and ROW, Power Query and VBA all get there. Answer1,275 Shortest=50*51/2 Excel 365=SUM(SEQUENCE(50)) 1. The maths formula =50*(50+1)/2Copy The sum of 1 to n is n(n+1)/2. … Read more

Find Day Name from Date in Excel

Day Name From a Date

To get the day of the week for a date, such as Tuesday for 28-Dec-1937, use =TEXT(B3,”dddd”). Here are four ways: two formulas, a number format that shows the day without changing the date, and Power Query, plus day names in Malayalam and Hindi. Watch the short video: Excel formula to get the Day name … Read more

Separate Positive and Negative Numbers in Excel

Separate Positive and Negative

To split a column of amounts into positive and negative columns, such as receipts and payments in a bank statement, use =MAX(B3,0) and =MIN(B3,0), an IF formula that leaves blanks, or FILTER for two separate lists. Power Query does it for large data. fx=MAX(B3,0) ProblemSplit amounts into positive and negative columns Functions usedMAX, MIN, IF, … Read more