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:
- 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.
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.
b) Select “More Commands…”
c) In the “Choose commands from” dropdown, select “Macros”
d) Select the macro you just created (“AutoFit”) and click the “Add” button
e) Optional: click the “Modify” button to change the icon that will be used. Select your new icon, then click the “OK” button
f) Click “OK” to close the Excel Options window
You will end up with an additional icon in your Quick Access Toolbar:
Now after pasting data in from the external source, just click the icon!