In Excel, go to Formulas > Name Manager > New. Enter MKT_CTR as the name, paste the full LAMBDA into Refers to, and save. Use =MKT_CTR(A2,B2) for one row, =MKT_CTR(A2:A20,B2:B20) for a traditional range, =MKT_CTR(A2:.A600,B2:.B600) for trailing Trim References, =MKT_CTR(A2#,B2#) for spilled ranges, and =MKT_CTR(A2:.A600,B2:.B600,1) for numeric output.
Marketing
MKT_CTR
Calculate CTR in Excel
Calculate click-through rate from clicks and impressions with formatted percent or numeric decimal output.
Syntax
=MKT_CTR(CLICKS, IMPRESSIONS, [NUMERIC])What It Does
What this function calculates
MKT_CTR calculates click-through rate for campaign exports and reporting models. It supports scalar values, regular ranges, named ranges, Trim References, and spilled ranges so the same function can work in single-cell checks or dynamic report tables.
Syntax
Function syntax
Syntax
=MKT_CTR(CLICKS, IMPRESSIONS, [NUMERIC])Interactive Demo
Try MKT_CTR
Edit the clicks or impressions below to see how MKT_CTR recalculates the result.
A
B
C
D
1
Campaign
Clicks
Impressions
CTR
2
3
4
5
6
Arguments
Inputs this function expects
| Argument | Required | Description | Example |
|---|---|---|---|
CLICKS | Required | The number of clicks recorded for the campaign, ad group, creative, or row. Can be a direct numeric value, compatible numeric text, single-cell reference, traditional range, named range, trailing Trim Reference, or Microsoft 365 spilled range. | 120 |
IMPRESSIONS | Required | The number of impressions associated with those clicks. Can be a direct numeric value, compatible numeric text, single-cell reference, traditional range, named range, trailing Trim Reference, or Microsoft 365 spilled range. | 50000 |
NUMERIC | Optional | Optional. Leave blank for presentation-ready text output. Enter 1 to return a numeric value suitable for calculations. The formula checks whether NUMERIC is supplied with ISOMITTED(NUMERIC), not whether the value specifically equals 1. | 1 |
Example
Example calculation
CLICKS120
IMPRESSIONS50000
Expected result0.24%
Clicks divided by impressions. The formatted default returns percent text; adding NUMERIC returns the decimal 0.0024 for calculations or charting.
Sample Data
Copy/paste-ready sample data
Sample Campaign Data
Copy and paste this campaign dataset into Excel starting at A1, then test the function against the metric columns.
| Clicks | Impressions | Campaign |
|---|---|---|
| 120 | 50000 | Search Brand |
| 310 | 84000 | Paid Social |
| 45 | 36000 | Display Prospecting |
| 900 | 120000 | Video Awareness |
| 0 | 0 | Zero Delivery Check |
Example Formula
=MKT_CTR(A2:.A600,B2:.B600)Expected outputWith the sample data pasted at A1, the formula spills formatted text results: 0.24%, 0.37%, 0.13%, 0.75%, and 0%. Add ,1 to return decimals such as 0.0024 for downstream calculations.
The same function can be used with A2:B6, A2:.A600 and B2:.B600, named ranges, or spilled inputs such as A2# and B2#.
Result
What the output means
Returns a formatted percentage string by default, or a numeric decimal when NUMERIC is supplied. Range inputs spill row by row. Zero impressions are replaced with 1 before division to avoid divide-by-zero errors, which can make unusual rows stand out for review.
Function Definition
LAMBDA definition
LAMBDA
=LAMBDA(CLICKS,IMPRESSIONS,[NUMERIC],IF(ISOMITTED(NUMERIC),
ROUND(IFERROR(VALUE(CLICKS),"")/IFERROR(VALUE(IF(IMPRESSIONS=0,1,IMPRESSIONS)),"")*100,2)&"%",
ROUND(IFERROR(VALUE(CLICKS),"")/IFERROR(VALUE(IF(IMPRESSIONS=0,1,IMPRESSIONS)),""),4)))Installation
Add this function to Excel
Troubleshooting
Common errors
#SPILL! means Excel cannot place the dynamic-array result; clear blocked cells below or beside the formula. #NAME? usually means the function was not saved in Name Manager or the name was typed differently. Numbers stored as text are generally handled with VALUE(), but non-numeric labels, extra symbols, or incompatible text can still return blanks or unexpected results. If ranges are mismatched, align the numerator and denominator ranges before using dynamic arrays. When the denominator is zero, this function substitutes 1 before division to avoid divide-by-zero errors; that can create unusually large or non-standard results and should be reviewed. Omit NUMERIC for formatted text output; enter 1 for numeric output. Because the formula uses ISOMITTED(NUMERIC), any supplied third argument triggers numeric mode.
Custom Function Request
Need a function that isn't here?
Tell me what you're trying to calculate.