Excel Tool to Automatically Format Your CSV Export

Have you exported a csv from AuthorityLabs and looked at it like, “What now?”. Don’t worry, every marketer has had this moment before.

whatnow

That’s where we come in! Now all you have to do is copy and paste the data you want to format into the template and run the macro we developed.

What are Macros?

According to Microsoft, “a macro is a series of commands and functions, a pre-written formula that takes a value, performs an operation, and returns a value.”

Which basically means you are able to record actions in Excel that can be played back later to take care of repetitive tasks. In our tool, we use macros to take away some of the intimidation of formatting your cvs export.

There are a few prerequisites:

Show Developer Tab

  • Click the Microsoft Office Button , and then click Excel Options, PowerPoint Options, or Word Options.
  • Click Popular, and then select the Show Developer tab in the Ribbon check box.

Enable Macros in Excel

  • Click the Microsoft Office Button , and then click Excel Option.
  • Click Trust Center, click Trust Center Settings, and then click Macro Settings.

Download the example files.

Now what?

Watch this short tutorial where I run you through how to run the macro in Excel. Enjoy!

Skimmers?

Here I will cover the step-by-step process with screencaps for you skimmers out there. Pro Tip: Select the “Enable Macros” prompt when you first open the file.

1. Select the Data

I held down ctrl+shift+down arrow and right arrow to select this data from my csv.

step 1

2. Paste the data into template

I recommend you also paste in your account, domain and start/end date data. (Which are cells A4-A11.)

step 2

3. Enable Developer Toolbar

File > Options > Customize Ribbon > Developer

step3 - developer

4. Navigate to the Macros

(Or just hit Alt + F8)

step 4

5. Run the macro

Make sure A1 is selected before running the macros. Then select the FormattingMacro and hit Run.

step 5

6. Finish!

You now have a fully formatted table and account data. Feel free to take it one step further and add in charts! Do you not know how? Well, funny you should bring that up. I have Excel 101 tutorial videos for you to watch!

step 6

About Tori Cushing

Tori Cushing is a Metrics Maverick at Authority Labs. She specializes in mining through mountains of data to find the precious jewels.

Filed under: AuthorityLabs Tips & Tricks, Tips

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>