Automatically Format an Excel File

Do you open the same type of Excel file every day, and make a few standard formatting changes, before you start to use it?

One of my clients was getting tired of manually formatting a daily list of customer information, and asked for some help. I suggested that she record a macro while formatting the file, and run the macro each day, when the new file arrives.

Other people in her company were in the same situation, so I set up a workbook with some fake data, and made a video, to show them the steps. I included a few Excel tips too, like using the F4 key to repeat the last action, and adding a button to the Quick Access Toolbar.

macrobuttonqat

Watch the Record and Run a Macro video

If it’s something that you’d like to learn, or share with a co-worker (so they’ll stop asking you for help!), you can watch this short Excel video tutorial.

_________________

You may also like...

9 Responses

  1. AlexJ says:

    Or…
    How about pre-formatting a template file and copying (or querying) the data into it?

  2. Kam says:

    This is a good solution but it may not work if the data is different every time.

    I tried this solution for data that I get and it didn’t work..I wanted to add 2 rows at each change in a column and then in the rows that were added, I wanted to insert headers so that I now had several individual tables. Finally I wanted to subtotal each table that I had created.

    Unfortunately a record macro didn’t work because I had different amounts of data every time..

    • @Kam, yes, in the video example, we’re only hiding columns, and they will be the same in every copy of the file. If you’re trying to make changes to the rows, you would need to modify the recorded macro, so it finds the specific rows that you want to format.

      • Kam says:

        Hmm I didn’t even know I could do that.. I will give it a try tomorrow and hopefully I will be successful, thanks.

  3. TANWEER says:

    i has some numerical numbers
    101000
    103001
    103002
    103003
    103004
    103005
    103006
    from these numbers, i want to select 2 numbers on a randum. like 103001 and 103005. here i will give these two numbers to excel and i want excel to identify this two numbers from the list and highlight with red colour

    is it possible ?

  4. Jimmy says:

    Select the range of cells and use conditional formatting:
    Condition 1 “Cell Value Is equal to” input cell reference #1
    Condition 2 “Cell Value Is equal to” input cell reference #2
    Set the format of the cell to red

  5. millie says:

    i want to enlarge the print and add the date, and company, I can’t get the page to scale; it says it must be set on automatic to do this, help?

Leave a Reply to Debra Dalgleish Cancel reply

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