Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm working with Excel 2010 and made over 20 tables in one sheet. I just want to work more efficiently by making a simple macro for auto formatting table. This is the style I want to apply with the macro:

  • Font: 10, Bold, Arial
  • Borders: All borders
  • Text: Center
  • Table: 3*5 (row * column)
  • Cell tile for header table only (any colors)
  • I've uploaded a sample table with that style, if my question is not clear enough.

Thanks for any help!

share|improve this question
Hi, do you mean to create a blank table or style an existing one? – matan129 Jul 2 '13 at 10:29
i just want to make auto stile table, the tables it`s exist, the target with my Q, is instead of doing many orders with the same type,manually, so this is consumed many times, if can make it auto by macro. – GIS Man Jul 2 '13 at 10:52
up vote 1 down vote accepted

You can do that without a macro. Start with a sample table. Click the Table Tools ribbon > Table Style drop down. Select a table style that is close to what you need. Right-click the table style and select "Duplicate".

In the following dialog, format each table style element as you desire, for example set the Header Row to a light yellow fill. Then tick the box "Set as Default Table quick style for this document".

Now you can create a new table and it will automatically have that style without running any macro. The new custom table style will be at the top of the list of table styles in the table tools ribbon.

share|improve this answer
thank you very much, @teylyn, i did your solution, but the formatting text, still without any changes. – GIS Man Jul 2 '13 at 11:36
Start with the default table style. Duplicate the style and set the header row to Yellow fill. Make the table style the default. Does that not work for you? Can you see the custom style in the table styles drop-down? Make sure you follow all steps. – teylyn Jul 2 '13 at 11:49

Record a macro via View > macros > Record Macro.... It captures your actions, so just style a table the way you want and when you are done recording click View > macros > Stop Recording.

Source: this tutorial

share|improve this answer
Please don't post only links. Links can go bad. Summarize the idea in your post, then provide the link as a reference. – teylyn Jul 2 '13 at 11:01
I'll fix it in a moment, thanks for the comment. – matan129 Jul 2 '13 at 11:02
thank you very much @matan129, i followed this solution before, but i faced an error message with it, when i am working with another table in the same sheet and click on macro bottom an error message appears, "run-time error 1004 A table cant overlap another table. – GIS Man Jul 2 '13 at 11:41
I don't know what to say then. Check out teylyn's answer, it's better then mine:) – matan129 Jul 2 '13 at 11:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .