How to Record and Run Macro to Format Excel File

http://www.contextures.com/excel-macr...

Instead of manually repeating the same steps every time you format an Excel file, you can record and run a macro, to automate the steps.

In this example, which has fake data from the FakeNameGenerator site, we're hiding worksheet columns, and adjusting the page setup, so the file will print at one page width.

Finally, we'll add a button to the Quick Access Toolbar, so the macro is easy to run.

See the steps in this short Excel tutorial.

---

Closed Caption:

in this video we'll see how to record
and then run a macro that lets you fix
up an excel file we receive this file in
the exact same format maybe every day or
every week you want to be able to print
it but without printing all the columns
so we'll see how you record the macro as
you hide the columns you don't need and
then when you get the next file you can
just run that same macro it will quickly
hide everything and you won't have to do
this manual task every time you get the
file so to start recording i'm going
down to the bottom left and click this
button that will start the recorder it
opens up a record macro window
we're going to name it fix excel file we
want to store this macro somewhere that
will always be available not just in
this workbook so i'm going to select
personal macro workbook you can write in
the description but I'll just leave that
blank and click ok so now this is
changed to a Blue Square talent bottom
i'm going to just start hiding some of
the columns
I'm just going to click on a heading for
column I want to hide and click hide now
that I've hidden one when i click on the
next one on my keyboard i can press the
f4 key and that will repeat the action
that I just did these are all in Canada
so I don't need that column i can select
a group of columns if I want to hide all
of them go through form all the steps
and now we're just left with a few
columns can make a cold narrower you can
adjust whatever you need and I can also
check to make sure this will print on
one page wide go to page layout page
setup section I'm going to click this
little pop-up and now for the page is
going to be portrait instead of a
hundred percent i want it to fit one
page wide we're going to clear out this
tall because i don't know how long the
list will be all i know is that i only
want it to be one page wyd now I could
do a preview and there it shows this is
page 1 of 14 close the preview by going
back to the Home tab so everything set
up and now i can stop recording have
clicked on the cell down at the bottom
left or click the blue square which will
stop there
according now to test it i can close
this without saving it
I've opened the same file again it's
unformatted now to run my macro that I
record it
I'll go to the View tab and over at the
far right you see macros click view
macros you can see the macros that are
in the personal workbook and here's the
one i recorded fix excel file click run
that very quickly hides all the columns
and does the page setup if I want that
easier to run i could put it on the
quick access toolbar and that is right
up at the top of my Excel window to add
something i'll click the customize more
commands from this list i'll choose
macros go down to the personal workbook
fix excel file add and now it's in this
list I want to have a different symbol i
could pick a green square i could pick
checkmark any symbol in this list of
buttons will go with the checkmark click
ok and I'll click OK down at the bottom
and now there's a check mark on my quick
access toolbar so i'm going to close
this file without saving and open it
again
so here's the file out any formatting
I'm now going to click my fix excel file
button on the quick access toolbar and
it's very quickly set up the way I want
it