How To Alter Microsoft Excel Templates

Question

I'm Creating a Gantt chart and I found this Microsoft excel template, I would like to make some edits to the colors and formulas behind the template to suit my needs, Can this be done?

Gantt Chart to be edited

Answer 1

The simple answer is yes but looking at the png, it is not easy to see whether the template has a macro attached to it. Assuming a standard template, the format is .xltx and if you want to make a lasting change, after downloading, save as file.xltx first. That tells the program to save the file in your template directory. In mine it is at C:\Users\username\Documents\Custom Office Templates. Then you can start changing the colours by either highlight a block, then right click > Format cells. This is the most comprehensive option as you have the option of changing almost everything in that block you have highlighted.

That gives you the option of amending fonts, alignment to formatting. But remember all the changes here will only affect the block you have highlighted.

To amend the spreadsheet on a whole sale basis you need to use the four blocks in the ribbon I have pointed at in the image below. These blocks will impact any area you highlight. To amend just the colour you need to use the cell styles ribbon which gives you the option of using accents which are finely graded colours.

To change the colours on the project template, I would think you will either use the conditional formatting block in the ribbon which allows you to use conditions in the cell to identify which colours and other formatting options you want to apply such as if the value of cell A1 is higher than x, use blue otherwise use red. using this option you can build conditions into your template which you data can pick up and work with. On the other hand if you just want a whole spreadsheet change, you can use the "Format as a table" option which just applies a blanket template - all table colouration including lines and backgrounds to the spreadsheet.

To change formulas, you will need the formulas tab of the spreadsheet which gives you a lot of options to apply. If you are going to save the file as a template and your formulas are complex enough to be macros, you may want to save the file as file.xltm.

In any case, any template can be changed to suit the user. That is the purpose of creating templates. They can be reused over and over again.

In reality, I suspect you will end up using all the options to fine tune the Gantt chart. Remember to save the spreadsheet after you have achieved the look you want so that you can then reuse it. Where you attach a macro to the template, it is saved as .xltm.

Answer 2

I've actually worked with this template - the colours in the Gannt are implemented using conditional formatting. The quickest way to alter the colours is to select a cell, go to conditional formatting > Manage Styles and play with the colours there. Then use format painter to apply it to the whole (chart) section. Other bit's such as the date fonts use defined styles (Title, explanatory etc).

There are no macros behind the template - it's all just formatting.