What is a Macro?
You may be wondering what the hullabaloo is about Google Sheets and Macros.
In fact, you may be wondering what a macro is. So lets start with that, and
discuss macros in a general sense.
Macros are found in many types of computer applications. The utility of a
macro is to allow the user of the application to record a series of steps that
they find they use often. I'm writing this web page using Linux VIM for
example. And if I decide I want to change all of my header lines to a different
size (h2 html tag instead of h3 perhaps), and also possibly modify the text of
each header, I usually record a VIM macro that I can invoke which will find and
adjust the next header in my HTML file, and leave me at that spot in the
document in case I want to further alter it. Then when finished with a header,
I can run the macro again to automatically find and alter the size of the next
While I don't like the term Power User, I suppose you could say that
Power Users generally learn to use macros if available in whatever applications
they often use. I prefer Experienced Users.
So if you want to move up a notch in the experience chain for Google Sheets
users, you may want to learn macros. With that knowledge, you can automate
most any sequence of steps you find yourself doing repeatedly and thus make
your future work easier and likely less error prone. And you can do this
without having to learn how to program scripts directly.
How to Create a Google Sheets Macro
Below is a screen shot of a portion of a Google Sheets workbook that has two
columns of data.
Now for a contrived example, assume these columns of data are side lengths
of a number of right triangles, and I want to compute the hypotenuse of each
triangle (square root of sum of squares). But, I expect to have files like this
often, and need to do the same operation each time. I can create a macro to
accomplish this. I'll construct a macro that creates columns of the squares of
the values before computing the hypotenuse. It's an unnecessary step
mathematically, but instructional as to creating macros.
The first thing I have to do is inform Google Sheets that I wish to record
the following sequence of steps as a macro. I do this by clicking on
Tools, then Macros, then Record macro. The following image
illustrates this step.
When I do these clicks, a pop up appears as shown in the image below that
lets me know I'm building a macro. It remains there until I'm finished, and
then allows me to save (or cancel) the macro.
On the macro window near the bottom of the illustration, you can see that I
have the option to choose either absolute references, or relative
references. If the data on which I'm going to run the macro is always
going to be in the same cells, I should pick absolute references. But
if I am going to build a macro that will be used on data that might be most
anyplace in a workbook, I should pick relative references. For this
contrived instance, I chose absolute references.
In my example, I want to insert a column between columns A and
B to allow a column for the squares of column A values. When
recording a macro, I can use most any spreadsheet function or operation that I
would normally use. So as the following image illustrates, I right-click on
column B and select insert 1 left. This inserts a column that
I'll use to hold the squares of column A values.
With the new column inserted (and the step recorded), I want to add labels
to the inserted column and two columns past column C. I also want to
compute the squares of the values in column A. I used the
ARRAYFORMULA function for this step, as you can see in the following
image. Again, as you see the macro indicator at the bottom of the image, you
know that the entry of the equation is being recorded in my macro.
In the following image you can see that I also added an array formula to
compute the squares of the second triangle side values, now residing in column
C, with the squares in column D. In the image you see that I'm
inserting the final formula that will sum the squares of the sides (sum columns
B and D) and take the square root. This will complete my macro.
After this equation executes, I click SAVE on the macro window at the
bottom of the image. It gives me the opportunity to provide a name for the
macro. I used test.
Now to test the macro, I cleared the workbook and started again with just
columns A and B, my triangle sides. To run the macro, I select
Tools, then Macros, then test, the name of the macro that
shows in the bottom of the Macros sub-window. Clicking on test will do
all of the steps recorded when the macro was created. Incidentally, the
first time you run your new macro, you'll probably be presented with a pop up
window that asks you to grant permissions to run the macro. This will only
happen once in that workbook. All subsequent uses of the macro will not ask
The following image shows the results of running the macro: new column
inserted, labels added to the columns, the squared value columns created, and
the hypotenuse column calculated.
You Can Edit Your Macro Scripts
In the following image you can see that I'm about the select the Tools
then Script editor. This will allow me to inspect the macro that Google
Sheets created for me. This is the same editor you could use to write your own
scripts of new functions. If you learn a little about the scripting, you can
use the script editor to make enhancements to your macros if you wish.
So, what kind of enhancements might you want to include in a macro? Well,
in the case of this simple macro, one obvious limitation is that it's
hard-wired to operate on 20 rows. If I loaded data with a different number of
rows, this macro would still expect to operate on exactly 20. I could enhance
it so that it could detect the size of a highlighted region, and figure out how
many rows are being operated on, and then do the computations in a loop instead
of using an ARRAYFORMULA, depending upon the number of detected rows.
Some Differences between Scripts and Macros
If you aren't into scripting in Google Sheets, it's pretty obvious what
advantage macros offer you. They let you automate processes without having
to know scripting. And even if you want to do some enhancements to your
macros, you only need to learn enough scripting to add those enhancements.
But if you are already into scripting in Google Sheets, you may wonder what
macros could do for you. It turns out they can do something for
you. You may have discovered that from within a function script that you've
created that you cannot affect any part of the current workbook other than the
cell the scripted function is entered into, and the below and to the right
adjacent cells should you return a matrix. But you cannot just change the
value of some other cell somewhere else in the workbook.
Turns out, with a macro, you can. A macro can have access to anywhere in
your current sheet. That's likely why you have to grant permission to run
the macro, but not to run your scripted functions.
A difference however that falls to the advantage of function scripts is that
they can be put into libraries of your own. Then, if you are in a completely
different workbook you can go to Tools then Script editor and
select Resources, where you can make reference to your libraries. You
still have to create functions within your script to call those library
functions, but they are just simple reference scripts.
For example, I have a library full of matrix routines. I call it
matlib. In the spreadsheet where I created the library, I can get a
reference number for that library from within the Script editor. I enter that
reference number into the Script editor Resources of my new spreadsheet,
to get access. Then, for example, to call my mbias function that will
let me add a bias to every element of a range, I add the calling function as
var ret = matlib.mbias(mat,v);
reference function is necessary because the current workbook
can only run what it sees in its own Script editor. It can't see directly the
functions in a library. The
reference functions I put in the workbook's
Script editor, however,
can call my library functions.
Any way, the point being that scripted functions can be placed into
libraries and referenced from other workbooks.
Macros -- cannot. That is, you cannot place macros in a library and thus
get to them from a different workbook.
As designed, macros live in the Script editor of the workbook in which
they were created. They can be invoked in any sheet of that workbook, but
cannot be placed in libraries and thus referenced in other workbooks.
You can go to a workbook whose Script editor has said macro, copy it to the
clipboard (highlight and CNTL-c), then go to the Script editor of the desired
workbook, and paste the macro there (CNTL-v).
If you do that, then when you go to the Tools/Macro menu, you'll see
You see that in the Record macro window, there is now an
Import option. The workbook has detected that there are more scripts in
the editor than just the test script we created earlier. Clicking on
Import in this case gives the following:
In this case, you see that I've pasted a whatif script from another
workbook into this workbook's Script editor, and now I can select this as an
additional macro to be available in this workbook. I will be asked to give
permission on the first attempt to use it, as it's never been used in
A little less convenient than the library feature available for regular
scripts, but not a big deal really. Just know that you can copy a macro from
one of your workbooks to another by just copying it from one workbook
Script editor to the other.
There is also another way to do this. When I am creating either multiple
macros that I use in working with some kind of data, or a complex macro that
gets a bit big, I do it in a test workbook. All that's in the test workbook is
some test data and the macros in the Script editor. If I know I'm going to be
working in a workbook that will need these macros, I just enter the workbook
where I designed the scripts, and save it as a copy, which I rename to be the
new sheet I want. I replace my test data with my real data, and all of my
macros are already there.
A Case in Point:
What If Macro
To illustrate the usefulness of macros and how worthwhile it is to
learn how to move them around, I present the What If macro.
A little history about the macro. I was proof-reading a paper my son
wrote for his master's work when I ran across a spreadsheet feature I was
totally unaware of. He did a Sensitivity Analysis of some data,
and sure enough in Excel with which he did his analysis there was a utility
just for that. I call it a utility in that it was a menu selection, not just
a function that you type into a cell.
It is a very clever utility. It allows you to create either 1D (single
column) or 2D (table) analysis tables where the utility dutifully replaces
one or two cells of some analysis area in your sheet with a range of other
exploratory values. It allows you to monitor any
result cell for
how it is affected by the varying exploratory values.
It's neat in that the utility simply substitutes the one or two cells in
some user created table with the exploratory values, lets the spreadsheet do
the analysis set up previously by the user, and then records in a column (1D)
or table (2D) the corresponding result values. Clever in that it doesn't care
what the analysis equations are or what they do, it just replaces a cell or two
and lets the spreadsheet do whatever was originally created to do, then obtains
the result of interest.
A What If Example
The following spreadsheet images gives a brain simple example.
Imagine we have the sheet shown above. It has just a few parameters for
a loan payment calculation. Cells B1 through B3 contain
respectively the yearly interest rate, the loan value, and the number of
payback periods. Cell B5 has the resulting payment, obtained using a
built-in spreadsheet function.
But suppose we'd like to explore the effect on payment of several different
interest rates. We could just manually keep changing the value in cell
B1 and looking at the result. But if we had the clever Sensitivity
Analysis utility, or what others call a What If utility, we could
easily build as many values as we want, and have them recorded in the
You see the start of that analysis in the above image. Below the initial
cells that contain the loan parameters and payment result is a column of
interest rates that we might like to examine. And if we were in a spreadsheet
that had such an analysis utility, it would be a piece of cake.
But if we are in love with Google Sheets because of it's low price (free)
and many conveniences, we are in a pickle. Google Sheets doesn't have such
a utility. Not built in anyway.
Google Sheets Add-ons To The Rescue
If you check out the Add-ons and look for what if you'll find
that there is a function that can come to the rescue. I've tried it, and it
works just like the Excel function. Just like it other than it is a bit slow.
On single column analysis it's not noticeable, but on a table of maybe a
hundred cells, it's a bit slow. But it works and is freely available.
However, I saw this as a perhaps a double opportunity. For one thing, I
thought such a utility would be nice to have. For another, I'd found that
recently (as of this writing), Google Sheets had added macros, and I thought
maybe this was a good opportunity to learn how to use them. I'd already
realized that I wouldn't be able to do this utility with a regular script
function because of the lack of permission to alter cells of an existing sheet.
So I tried the macro solution.
I could see that setting up a macro to step by step to place the exploratory
values into the interest cell (B1) and placing the payment result
back next to the respective interest value was doable, but tedious. And
the resulting macro would only work for a specific size table and specific
locations of the user table elements.
So I did a few of the exploratory values just to see the nature of the
resulting script. Then, because I had some experience with scripting functions,
I hunted down a few Google Sheets API functions I needed and turned the macro
into a generic macro that would work much like the Add-ons utility. I got it
to work, and surprisingly, it ran about 4 times faster than the Add-ons
version. So I use it.
The next few images show how the macro works. First, as the previous image
shows, I created two columns, one for the interest values to be explored, and
one for the Payments that would result. I also placed in the first cell of my
interest column the cell address for where the interest parameter is stored,
B1. The address reference is stored just as a text string.
Below that I put a reference to the result cell that I wish to monitor,
payment in this case. So I put =B5 in cell C10. Then below that
I put a column of exploratory interest values I was interested in.
To run the macro, I highlighted the indicated region to include the cell
reference of the parameter I want to change, down through the last interest
rate, and including also the column for the results. Then I just, as
indicated, click on Tools, then Macros, then my whatif
Then I sit back and watch as the magic happens. Each exploratory value is
placed in the Interest cell at B1, and each result is entered next to
the respective exploratory interest rate, resulting the the sheet shown below.
A 2-dimensional What If Table Example
The macro checks what is highlighted before it runs, and if it sees more
than 2 columns highlighted, it assumes you are indicating a 2D table of values,
with the 1st column being exploratory values for one cell, and the 1st row of
the data area (one below the cell parameter addresses) being the exploratory
values for the second cell. Such a setup would look like the image below.
As the example illustrates, two cell indicators must be provided. Next to
the B1 reference is a B3 reference, indicating that both interest
rate and number of periods are to be explored. Down the first column is the
list of interest rates which will sequentially replace the interest value, and
along the first row of the data portion of the table are number of period
values which will replace the Nperiods portion of the initial user sheet area.
Again, just below the first cell indicator is the reference to the result cell,
payment (B5) in this case.
To run the macro, I highlight from the first cell indicator to the bottom
right to include all interest values and all number of periods values. The
following image illustrates.
With the area highlighted, I'm ready to click on the macro again. Clicking
on it causes all cells of the table to be filled with payments corresponding to
the combination of interest and periods of the respective row/col. The
following image shows the result.
So in just a few seconds time I have a table of payment values exploring
several combinations of interest and number of payment periods. Cool
This macro is likely a bit more that you will make at first, mainly because
in order to
generalize it so that it would work with different sized
What If tables, some coding had to be done after the skeletal macro
was created. But given what the macro accomplishes, it shows the kind of
power that can be had with macros.
So in this utility, you can have your choice. You can use the Add-ons
what if function, which operates very similarly (except you must enter
the exploratory cell addresses into a prompt each time), or you can download
the script offered here and use it as a macro. The macro requires the
exploratory cell address(s) to just be part of the range on which it's
operating, no prompts. With the macro version, you have the code in your hands,
so to speak. You can learn from it, and very likely improve it.
I offer no warranty that the macro will work for you or
not even mess up your spreadsheet. I accept no responsibility in either
But if you want to experiment with it just to get an idea how you can
generalize your own macros, and possibly get some valuable use out of this one,
here it is: What If macro.
Just copy it and paste it into the Script editor of your workbook (I'd advise
using test data to start with), use the Tools / Macros / Import sequence
to make it available, and give it a shot.
Linux Limerick Gifts