Named Functions<\/a> in Google Sheets, you should give it a try. It’s a very useful feature for users who like to use formulas and functions in Google Sheets.<\/span><\/p>\nIn order to learn how to use Named Functions, check out this comprehensive guide and start creating custom functions in Google Sheets.<\/span><\/p>\n[\/et_pb_text][et_pb_text module_id=”what” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
What are Named Functions in Google Sheets?<\/span><\/h2>\nAs you know, Google Sheets has some powerful functions and formulas that allow you to perform complex calculations. In fact, you can combine multiple functions and create even more complex formulas.<\/span><\/p>\nIf you use the same custom functions on a regular basis, you can convert these frequently-used formulas into Named Functions. With this Google Sheets feature, you can create custom formulas, save them and even share them with other users.<\/span><\/p>\n[\/et_pb_text][et_pb_text module_id=”why” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Why use Named Functions in Google Sheets?<\/span><\/h2>\nHere are some benefits of using Named Functions in Google Sheets:<\/span><\/p>\n\n- If you customize formulas and use them on a regular basis, Named Functions can help you save time.<\/span><\/li>\n
- If you use large formulas frequently and struggle to type it out correctly, this feature can prevent you from making syntax errors.<\/span><\/li>\n
- If you need other team members to use the same custom formulas, you can create a named function and share it with them.<\/span><\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text module_id=”how-to-create” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to create Named Functions in Google Sheets<\/span><\/h2>\nLet’s say we have created a spreadsheet to collect and analyze data from our marketing campaigns. There are some frequently-used metrics we need to calculate when we want to analyze the results of these campaigns: CPA (cost per acquisition), ROAS (return on ad spend) and conversion rate.<\/span><\/p>\nHere, we are going to use a Named Function to create a simple formula to calculate the cost per acquisition. Keep in mind that this is just a simple example to help you get your feet wet. Once you learn how to use Named Functions in Google Sheets, you can create complex functions using built-in Sheets formulas.<\/span><\/p>\nIn order to calculate the CPA, we need to divide the total ad spend by the total number of conversions. Since it’s a sales campaign, the total number of conversions will be the total number of purchases.<\/span><\/p>\nFirst, go to <\/span>Data<\/b> and select <\/span>Named functions<\/b>.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_1-1.png” alt=”Named functions 1″ title_text=”named_function_1″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Now click <\/span>Add new function<\/b>.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_2-1.png” alt=”Named functions 2″ title_text=”named_function_2″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Write the function name and add a function description to let users know what it does. I’ll call it <\/span>CPA <\/b>and include a description: <\/span>Calculate the cost per acquisition<\/b>.<\/span><\/p>\nThen I can copy the formula I used to calculate the cost per acquisition and paste it into the formula definition field.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_3-1.png” alt=”Named functions 3″ title_text=”named_function_3″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The first argument in the formula is A2, which represents the first value: total spend. I’ll click A2 to add an argument placeholder. I’ll call it <\/span>ad_spend<\/b>.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_4-1.png” alt=”Named functions 4″ title_text=”named_function_4″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Now let’s define the second argument placeholder. I’ll call it <\/span>purchases<\/b>.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_5-1.png” alt=”Named functions 5″ title_text=”named_function_5″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As you can see, I have already created the custom formula: CPA = ad spend\/purchases.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_6-1.png” alt=”Named functions 6″ title_text=”named_function_6″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
When you click <\/span>Next<\/b>, you will visualize the Named Function.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_7-1.png” alt=”Named functions 7″ title_text=”named_function_7″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
If you type the Named Function in a cell now, you will see the description.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_8-1.png” alt=”Named functions 8″ title_text=”named_function_8″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
You can also check the formula syntax.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_9-1.png” alt=”Named functions 9″ title_text=”named_function_9″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
If you replace the argument placeholders, you will be able to calculate the cost per acquisition using this Named Function.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_10-1.png” alt=”Named functions 10″ title_text=”named_function_10″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Voil\u00e0! Google Sheets has calculated the cost per acquisition using the Named Function I have created.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_11-1.png” alt=”Named functions 11″ title_text=”named_function_11″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In this example, you could replace the placeholders with values instead of cells.\u00a0<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_12-1.png” alt=”Named functions 12
\n” title_text=”named_function_12″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”convert” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Convert your frequently-used formulas into Named Functions<\/span><\/h2>\nNow that you know how to use Named Functions, you can convert your frequently-used formulas and share it with other spreadsheets. We’ll use a more complex formula now to help us decide how to optimize our campaigns.\u00a0<\/span><\/p>\nWhenever we apply this formula, Google Sheets will return a command to tell us what we should do next: scale the campaign, test more ads or pause the campaign.<\/span><\/p>\nHere’s the formula we’ll use:<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_13-1.png” alt=”Named functions 12
\n” title_text=”named_function_13″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
If my ROAS (return on ad spend) is higher than 2.5x, Google Sheets will return the word <\/span>SCALE<\/b>. If it’s between 2.2x and 2.5x, I’ll need to test more ad creatives. Otherwise, I’ll have to pause the ad campaign.<\/span><\/p>\nNow we can follow the same steps now to create this Named Function. I’ll call it OPTMIZE<\/strong>.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_14-1.png” alt=”Named functions 12
\n” title_text=”named_function_14″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Then I’ll replace “K2” with the argument placeholder <\/span>ROAS<\/b>.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_15-1.png” alt=”Named functions 12
\n” title_text=”named_function_15″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Whenever I need to apply this formula, I can use this Named Function. This will make it much easier to use this custom formula. When I type in the formula, all I have to do is replace the argument placeholder <\/span>ROAS<\/b>.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_16-1.png” alt=”Named functions 12
\n” title_text=”named_function_16″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
By using this custom formula, Google Sheets will tell me how I should optimize the campaign based on the criteria I have established.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_17-1.png” alt=”Named functions 12
\n” title_text=”named_function_17″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Now I can export these Named Functions to other spreadsheets and even share them with my team members.\u00a0<\/span><\/p>\nLet’s say I want to create a new spreadsheet and import this function into it. I’ll open my new file and click <\/span>Import function<\/b> instead of selecting <\/span>Add new function<\/b>.<\/span><\/p>\nThen I’ll select the spreadsheet I want to import this function from. After choosing the Named Functions I want to import, I can start using this custom formula within my new spreadsheet.<\/span><\/p>\n[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/06\/named_function_18-1.png” alt=”Named functions 12
\n” title_text=”named_function_18″ _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”how-to-use” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n