Excel - Wrap New Function Around Each Formulas in Range: Episode 1713
949 views · Published 21 May 2013 · 1:34 · Indexed 1 November 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: Add a new function wrapped around each formula in a range. Welcome to another episode of the MrExcel podcast! In today's episode, we will be discussing a useful Excel tip - how to add a function around all formulas in a range. This is a topic we previously covered in episode 1705, where I shared a VBA solution. However, thanks to a suggestion from JohnS, we have a non-VBA way to achieve the same result using the FORMULATEXT function. To use this method, you will need to have Excel 2013 or a newer version. John's suggestion is to add the opening part of the function, followed by the FORMULATEXT of the original formula. However, we need to start at position 2 to remove the leading equal sign, and then make the formula long enough to handle any formula, and finally add the closing part. But here's where I'll make a slight modification - I'll add a ^ before the equal sign. This will ensure that the formula is not recognized as a formula initially, allowing us to copy and paste it throughout the range. Once we have the formulas copied, we can then paste them as values. After that, a simple Ctrl+H will replace every occurrence of ^= with =, effectively converting all the values back to formulas. It's a simple and efficient way to add a function around all formulas in a range, and I have to thank John for sharing this brilliant use of the FORMULATEXT function in Excel 2013. I hope you found this tip helpful and will be able to use it in your own Excel projects. Thank you for tuning in to this episode of the MrExcel podcast. Don't forget to subscribe to our channel for more useful Excel tips and tricks. I'll see you next time for another netcast from MrExcel! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Adding a Function Around Each Formulas in a Range (00:23) Second method using FORMULATEXT (00:35) Start at 2nd character of FormulaText result (00:58) Using a carat before formula (01:11) Converting Back to Formulas (01:21) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformulas This video answers these common search terms: nest a new function around each formula in a range wrap a new function around each formula in a range excel apply new function to all formulas wrap function around multiple excel formulas add function to all formulas in excel range apply a new function to multiple excel cells excel add function to existing formulas how to use formulatext function in excel wrap function around multiple cells in excel insert function into multiple excel equations wrap a function around a range of excel formulas nested functions how to excel apply new function to multiple cells in excel insert function into excel range of calculations In Episode #1705, Bill addressed the question of wrapping a group of existing Formulas with one New Formula with the use of VBA [Visual Basic for Applications]. However, today in Episode #1713, Bill shows us an interesting Formulaic solution to this question. And for more information on Excel 2013, check out "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. http://www.mrexcel.com/store/index.php?l=product_detail&p=254 "The Learn Excel from MrExcel Podcast Series" Visit us: MrExcel.com for all of your Microsoft Excel Needs! Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/learn-excel-from-mrexcel-add-function-around-all-formulas-in-range-podcast-1713.1153535/ About MrExcel: Bill Jelen is an Excel MVP and the author of 70+ books on Excel. He has trained thousands of professionals worldwide.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920