A time saver to help to format data in MS Excel

Quite often I copy data from one source (specifically, MS Access) and paste it into MS Excel. But when I do that, the columns and rows aren’t big enough. The data is thus unreadable and I have to modify the size of the rows and columns.

This is how I manually format the data:

Image for post
Image for post
Manually formatting the data
  • Highlight all of the data by clicking the top left corner (between “A” and “1”)
  • Click between two columns and drag the width several pixels
  • Double-click between two column headers (where you were just dragging)
  • Repeat for the rows

While it only takes a few seconds, I do this often enough that I made an Excel VBA macro. Placing a shortcut to it in the “Quick Access Toolbar” saves a surprising amount of time!

Step 1: Write the Code

a) In a new/blank Excel file: Developer Tab >> View Code (keyboard shortcut on Windows is Alt + F11)

b) Paste in this code as a new function/subroutine:

c) Save the file, but change the “type” to be an “Excel Add-In”. Upon doing this, the save location may change to a Microsoft-specific folder; if that happens, save it there. Note; if you used the format “Excel Macro-Enabled Workbook” instead, then Excel may not load it automatically in the future.

Image for post
Image for post

Step 2: Add to the Quick Access Toolbar

a) In the Quick Access Toolbar, click the item to the far-right indicating you want to Customize. Note: your toolbar may be at the very top of the screen; mine is below the Office Ribbon.

Image for post
Image for post

b) Select “More Commands…”

Image for post
Image for post

c) In the “Choose commands from” dropdown, select “Macros”

Image for post
Image for post

d) Select the macro you just created (“AutoFit”) and click the “Add” button

Image for post
Image for post

e) Optional: click the “Modify” button to change the icon that will be used. Select your new icon, then click the “OK” button

Image for post
Image for post

f) Click “OK” to close the Excel Options window

Image for post
Image for post

You will end up with an additional icon in your Quick Access Toolbar:

Image for post
Image for post

Now after pasting data in from the external source, just click the icon!

Image for post
Image for post

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store