MrExcel Publishing
Your One Stop for Excel Tips & Solutions
Microsoft MVP

Use Natural Language Formulas Without Using Natural Language Formulas

February 24, 2021 - by Bill Jelen

Challenge: Excel 2003 offers relatively obscure natural language formulas, but they were removed from Excel 2007. The table nomenclature in Excel 2007 isn’t as easy to use.

Solution: To solve this problem, you can use the intersection character in your SUM function. Everyone knows that =SUM(A2:A10) sums the nine-cell range from A2 through A10. Most people realize that =SUM(A1,A3,A5,A7,A9) adds up the five cells specified. However, very few people understand that the space character is actually an intersection operator when used in a SUM function!

continue reading »

More Reading

Always Point To Cell B10 »

February 17, 2021 - by Bill Jelen

You want to create a formula that always points to cell B10. Normally, if you have a formula that points to B10 or even $B$10, the formula changes if you cut and paste B10 or if you insert or delete rows above row 10.

Use Indirect To Get A Data From A Multi-cell Range »

February 15, 2021 - by Bill Jelen

INDIRECT is pretty cool for grabbing a value from a cell. Can INDIRECT point to a multi-cell range and be used in a VLOOKUP or SUMIF function?

read more articles »

Featured Products

Excel Data Cleansing Straight To The Point

Excel Data Cleansing Straight To The Point »

This Straight to the Point guide provides an introduction to data cleansing, which also goes by names such as data munging and data wrangling. Whatever the name, it basically means doing what needs to be done to make data useful and trustworthy.

MrExcel 2020 - Seeing Excel Clearly

MrExcel 2020 - Seeing Excel Clearly »

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.

Microsoft Excel 2019 Pivot Table Data Crunching

Microsoft Excel 2019 Pivot Table Data Crunching »

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.

Power Excel With MrExcel - 2019 Edition

Power Excel With MrExcel - 2019 Edition »

This is the print book edition of "Power Excel with MrExcel - 2019 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.

The Absolute Guide to Dashboarding & Reporting with Power BI

The Absolute Guide to Dashboarding & Reporting with Power BI »

Learn Power BI Dashboarding Techniques by Watching A Pro!

Excel Dynamic Arrays Straight to the Point 2nd Edition

Excel Dynamic Arrays Straight to the Point 2nd Edition »

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.

see more products »