Adjusting, Formatting and Converting Data Into a Table

Have you ever received data or copied/downloaded data from another source, and discovered it is not in a usable format?  A few issues that happen consistently with new data are:

  • Columns and rows are not adjusted (the # symbol indicates a column that is not wide enough to display the formatted dates).
  • Text data is chopped off due to insufficient column widths.
  • Row heights are inconsistent and unnumbered, making it harder to keep the original sequence of data.

 

 

The following shortcuts can be performed in a sequence to fix the previous structure and create a formatted table that includes numbered rows.

Note:  If this is a repetitive sequence of steps, you can create a macro that saves the steps, and when you run the macro, the steps will be performed for you (taught in Excel Level 3, L&OD).

Shortcuts to Perform:
  1. Select the whole table (CTRL + A )
     Adjust the Column widths (ALT H  + O + I )
  2. Select the whole table (CTRL + A )
    Adjust the Row Heights (ALT H + O + A )
  3. Insert a New Column A (CTRL Spacebar, CTRL SHIFT and ‘+’)
  4. Insert Row Numbers (Type 1 and 2 in new cells A5 and A6, Select cells, and Double-Click on Autofill handle)
  5. Convert Range to a Table Object (CTRL + T, Press ENTER)