Is it possible to save conditional formatting rules for reuse in a new workbook?

I want to make some rules that I can haves saved that I can easily apply to new workbooks as needed. It's a pain doing it how I currently do where I'm always having to go back through and recreate these conditional format rules. If this is unclear please let me know and I'll try explaining better.

My apologies... so here is a better description of the issue.

I have values that I want to color code that come up all the time in documents I create. For instance, I might have a sample document like the following:

Jane       2.1

Steve      4.5

Caleb      4.4

I want to have the cells with the numbers formatted a certain way based on the numbers falling within a certain range. So each time this comes up in a document I end up created 7+ conditional rules for the 7 or more number ranges. These rules never change except for every 3 years or so. It would be nice to be able to have them save and then I can just use format painter or something to apply them to certain columns when creating a new document.

Hope this helps explain the situation!

edited Apr 14 '13 at 0:56

Brad Patton

9,181123367

asked Apr 11 '13 at 13:20

fwaokda

1894514

Can I save conditional formatting rules for use in other workbooks? [Excel 2010] answers.microsoft.com/en-us/office/forum/office_2010-excel/… Easily found by Googling 'excel save conditional formatting'

– Jan Doggen
Apr 11 '13 at 14:03

You could store your rules in VBA macro. If you have no experience with VBA try to start recording before you will set CF rules next time. Don't be surprised- if you have complicated CFs that you could result with complicated (but understandable) code. If it is something easy try @JanDoggen link with a bit help of recorder.

– Kazimierz Jawor
Apr 11 '13 at 14:06

add a comment |

My apologies... so here is a better description of the issue.

I have values that I want to color code that come up all the time in documents I create. For instance, I might have a sample document like the following:

Jane       2.1

Steve      4.5

Caleb      4.4

Hope this helps explain the situation!

edited Apr 14 '13 at 0:56

Brad Patton

9,181123367

asked Apr 11 '13 at 13:20

fwaokda

1894514

Can I save conditional formatting rules for use in other workbooks? [Excel 2010] answers.microsoft.com/en-us/office/forum/office_2010-excel/… Easily found by Googling 'excel save conditional formatting'

– Jan Doggen
Apr 11 '13 at 14:03

You could store your rules in VBA macro. If you have no experience with VBA try to start recording before you will set CF rules next time. Don't be surprised- if you have complicated CFs that you could result with complicated (but understandable) code. If it is something easy try @JanDoggen link with a bit help of recorder.

– Kazimierz Jawor
Apr 11 '13 at 14:06

add a comment |

My apologies... so here is a better description of the issue.

I have values that I want to color code that come up all the time in documents I create. For instance, I might have a sample document like the following:

Jane       2.1

Steve      4.5

Caleb      4.4

Hope this helps explain the situation!

edited Apr 14 '13 at 0:56

Brad Patton

9,181123367

asked Apr 11 '13 at 13:20

fwaokda

1894514

My apologies... so here is a better description of the issue.

I have values that I want to color code that come up all the time in documents I create. For instance, I might have a sample document like the following:

Jane       2.1

Steve      4.5

Caleb      4.4

Hope this helps explain the situation!

microsoft-excel conditional-formatting

edited Apr 14 '13 at 0:56

Brad Patton

9,181123367

asked Apr 11 '13 at 13:20

fwaokda

1894514

edited Apr 14 '13 at 0:56

Brad Patton

9,181123367

asked Apr 11 '13 at 13:20

fwaokda

1894514

edited Apr 14 '13 at 0:56

Brad Patton

9,181123367

edited Apr 14 '13 at 0:56

Brad Patton

9,181123367

edited Apr 14 '13 at 0:56

Brad Patton

9,181123367

asked Apr 11 '13 at 13:20

fwaokda

1894514

asked Apr 11 '13 at 13:20

fwaokda

1894514

asked Apr 11 '13 at 13:20

fwaokda

1894514

Can I save conditional formatting rules for use in other workbooks? [Excel 2010] answers.microsoft.com/en-us/office/forum/office_2010-excel/… Easily found by Googling 'excel save conditional formatting'

– Jan Doggen
Apr 11 '13 at 14:03

You could store your rules in VBA macro. If you have no experience with VBA try to start recording before you will set CF rules next time. Don't be surprised- if you have complicated CFs that you could result with complicated (but understandable) code. If it is something easy try @JanDoggen link with a bit help of recorder.

– Kazimierz Jawor
Apr 11 '13 at 14:06

add a comment |

Can I save conditional formatting rules for use in other workbooks? [Excel 2010] answers.microsoft.com/en-us/office/forum/office_2010-excel/… Easily found by Googling 'excel save conditional formatting'

– Jan Doggen
Apr 11 '13 at 14:03

You could store your rules in VBA macro. If you have no experience with VBA try to start recording before you will set CF rules next time. Don't be surprised- if you have complicated CFs that you could result with complicated (but understandable) code. If it is something easy try @JanDoggen link with a bit help of recorder.

– Kazimierz Jawor
Apr 11 '13 at 14:06

Can I save conditional formatting rules for use in other workbooks? [Excel 2010] answers.microsoft.com/en-us/office/forum/office_2010-excel/… Easily found by Googling 'excel save conditional formatting'

– Jan Doggen
Apr 11 '13 at 14:03

You could store your rules in VBA macro. If you have no experience with VBA try to start recording before you will set CF rules next time. Don't be surprised- if you have complicated CFs that you could result with complicated (but understandable) code. If it is something easy try @JanDoggen link with a bit help of recorder.

– Kazimierz Jawor
Apr 11 '13 at 14:06

add a comment |

3 Answers
3

active

oldest

votes

It looks like you’ve got the answer right there in your question –– or am I missing something?
You can use “Format Painter” (in the “Clipboard” panel of the “Home” tab).

Create a file that has your chosen formats. Save it. ... Later,

Create or open a new file with data.

Reopen your original file.

Click on a cell that has the format(s) that you want to use.

Click on “Format Painter”.

Switch over to the new file and click on the cell(s) to which you want to apply the format(s).

Following standard “Format Painter” semantics, if you want to copy a format (or a format collection) to multiple ranges, double-click on “Format Painter”.

answered Apr 11 '13 at 22:36

Scott

15.9k113990

I think the OP was about using conditional formatting which I don't think Format Painter copies.

– Brad Patton
Apr 12 '13 at 0:23

@Brad: It does in Excel 2007.

– Scott
Apr 12 '13 at 0:25

ahh it does in 2010 as well. But it appears to change the data range. If I have a rule for the entire worksheet and 'paint' it to another worksheet the range changes to the cells I paint which may or may not be fine for this case.

– Brad Patton
Apr 12 '13 at 0:33

Well, that is what I said: “6. Switch over to the new file and click on the cell(s) to which you want to apply the format(s).”

– Scott
Apr 12 '13 at 0:36

This copies all formatting, though, and/or deletes existing formatting. It doesn't just copy the conditional formatting rules. I want a way to save a set of rules with a name and then apply that rule to a selection, the way you can drop down and choose a color scale, for instance.

– endolith
Sep 11 '13 at 20:17

add a comment |

You can use a template to save the all of the conditional formatting rules.

Start with a blank spreadsheet.

Add any content you want to appear in new spreadsheet.

Add the conditional formatting rules you want in each new spreadsheet.

Use Save As and choose Excel Template

When creating a new spreadsheet choose My templates and then the template you want.

template

answered Apr 11 '13 at 14:19

Brad Patton

9,181123367

But how do you save and reuse the conditional formatting rules?

– endolith
Sep 11 '13 at 20:04

@endolith: Based on your requirements (which, AFAIK, cannot be satisfied), Brad’s answer is even worse than mine. In step 4, he saves a copy of the workbook, complete with all formatting, as a template file. And in step 5 he creates a new workbook that is a copy (duplicate / clone) of the original workbook, complete with all formatting.

– Scott
Sep 11 '13 at 22:46

@Scott: Yeah I created a macro and made a button for it, which seems to be working well

– endolith
Sep 12 '13 at 14:22

add a comment |

record a macro,run it the next time you have to apply the conditional format.

answered Apr 12 '13 at 0:43

truthurt

693

I recorded a macro of applying 2 conditional formattings to a range, saved to Personal Macro Workbook, then followed these directions for making a quick button. Now I can select a range and click the button and the conditional formatting is applied. It only applies to the selected range (color-highlight extreme values), so it works correctly.

– endolith
Sep 12 '13 at 14:51

add a comment |

protected by Community♦ Feb 1 at 17:31

Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

It looks like you’ve got the answer right there in your question –– or am I missing something?
You can use “Format Painter” (in the “Clipboard” panel of the “Home” tab).

Create a file that has your chosen formats. Save it. ... Later,

Create or open a new file with data.

Reopen your original file.

Click on a cell that has the format(s) that you want to use.

Click on “Format Painter”.

Switch over to the new file and click on the cell(s) to which you want to apply the format(s).

Following standard “Format Painter” semantics, if you want to copy a format (or a format collection) to multiple ranges, double-click on “Format Painter”.

answered Apr 11 '13 at 22:36

Scott

15.9k113990

I think the OP was about using conditional formatting which I don't think Format Painter copies.

– Brad Patton
Apr 12 '13 at 0:23

@Brad: It does in Excel 2007.

– Scott
Apr 12 '13 at 0:25

ahh it does in 2010 as well. But it appears to change the data range. If I have a rule for the entire worksheet and 'paint' it to another worksheet the range changes to the cells I paint which may or may not be fine for this case.

– Brad Patton
Apr 12 '13 at 0:33

Well, that is what I said: “6. Switch over to the new file and click on the cell(s) to which you want to apply the format(s).”

– Scott
Apr 12 '13 at 0:36

This copies all formatting, though, and/or deletes existing formatting. It doesn't just copy the conditional formatting rules. I want a way to save a set of rules with a name and then apply that rule to a selection, the way you can drop down and choose a color scale, for instance.

– endolith
Sep 11 '13 at 20:17

add a comment |

It looks like you’ve got the answer right there in your question –– or am I missing something?
You can use “Format Painter” (in the “Clipboard” panel of the “Home” tab).

Create a file that has your chosen formats. Save it. ... Later,

Create or open a new file with data.

Reopen your original file.

Click on a cell that has the format(s) that you want to use.

Click on “Format Painter”.

Switch over to the new file and click on the cell(s) to which you want to apply the format(s).

Following standard “Format Painter” semantics, if you want to copy a format (or a format collection) to multiple ranges, double-click on “Format Painter”.

answered Apr 11 '13 at 22:36

Scott

15.9k113990

I think the OP was about using conditional formatting which I don't think Format Painter copies.

– Brad Patton
Apr 12 '13 at 0:23

@Brad: It does in Excel 2007.

– Scott
Apr 12 '13 at 0:25

ahh it does in 2010 as well. But it appears to change the data range. If I have a rule for the entire worksheet and 'paint' it to another worksheet the range changes to the cells I paint which may or may not be fine for this case.

– Brad Patton
Apr 12 '13 at 0:33

Well, that is what I said: “6. Switch over to the new file and click on the cell(s) to which you want to apply the format(s).”

– Scott
Apr 12 '13 at 0:36

This copies all formatting, though, and/or deletes existing formatting. It doesn't just copy the conditional formatting rules. I want a way to save a set of rules with a name and then apply that rule to a selection, the way you can drop down and choose a color scale, for instance.

– endolith
Sep 11 '13 at 20:17

add a comment |

It looks like you’ve got the answer right there in your question –– or am I missing something?
You can use “Format Painter” (in the “Clipboard” panel of the “Home” tab).

Create a file that has your chosen formats. Save it. ... Later,

Create or open a new file with data.

Reopen your original file.

Click on a cell that has the format(s) that you want to use.

Click on “Format Painter”.

Switch over to the new file and click on the cell(s) to which you want to apply the format(s).

Following standard “Format Painter” semantics, if you want to copy a format (or a format collection) to multiple ranges, double-click on “Format Painter”.

answered Apr 11 '13 at 22:36

Scott

15.9k113990

It looks like you’ve got the answer right there in your question –– or am I missing something?
You can use “Format Painter” (in the “Clipboard” panel of the “Home” tab).

Create a file that has your chosen formats. Save it. ... Later,

Create or open a new file with data.

Reopen your original file.

Click on a cell that has the format(s) that you want to use.

Click on “Format Painter”.

Switch over to the new file and click on the cell(s) to which you want to apply the format(s).

Following standard “Format Painter” semantics, if you want to copy a format (or a format collection) to multiple ranges, double-click on “Format Painter”.

answered Apr 11 '13 at 22:36

Scott

15.9k113990

answered Apr 11 '13 at 22:36

Scott

15.9k113990

answered Apr 11 '13 at 22:36

Scott

15.9k113990

answered Apr 11 '13 at 22:36

Scott

15.9k113990

I think the OP was about using conditional formatting which I don't think Format Painter copies.

– Brad Patton
Apr 12 '13 at 0:23

@Brad: It does in Excel 2007.

– Scott
Apr 12 '13 at 0:25

ahh it does in 2010 as well. But it appears to change the data range. If I have a rule for the entire worksheet and 'paint' it to another worksheet the range changes to the cells I paint which may or may not be fine for this case.

– Brad Patton
Apr 12 '13 at 0:33

Well, that is what I said: “6. Switch over to the new file and click on the cell(s) to which you want to apply the format(s).”

– Scott
Apr 12 '13 at 0:36

This copies all formatting, though, and/or deletes existing formatting. It doesn't just copy the conditional formatting rules. I want a way to save a set of rules with a name and then apply that rule to a selection, the way you can drop down and choose a color scale, for instance.

– endolith
Sep 11 '13 at 20:17

add a comment |

I think the OP was about using conditional formatting which I don't think Format Painter copies.

– Brad Patton
Apr 12 '13 at 0:23

@Brad: It does in Excel 2007.

– Scott
Apr 12 '13 at 0:25

ahh it does in 2010 as well. But it appears to change the data range. If I have a rule for the entire worksheet and 'paint' it to another worksheet the range changes to the cells I paint which may or may not be fine for this case.

– Brad Patton
Apr 12 '13 at 0:33

Well, that is what I said: “6. Switch over to the new file and click on the cell(s) to which you want to apply the format(s).”

– Scott
Apr 12 '13 at 0:36

This copies all formatting, though, and/or deletes existing formatting. It doesn't just copy the conditional formatting rules. I want a way to save a set of rules with a name and then apply that rule to a selection, the way you can drop down and choose a color scale, for instance.

– endolith
Sep 11 '13 at 20:17

I think the OP was about using conditional formatting which I don't think Format Painter copies.

– Brad Patton
Apr 12 '13 at 0:23

@Brad: It does in Excel 2007.

– Scott
Apr 12 '13 at 0:25

ahh it does in 2010 as well. But it appears to change the data range. If I have a rule for the entire worksheet and 'paint' it to another worksheet the range changes to the cells I paint which may or may not be fine for this case.

– Brad Patton
Apr 12 '13 at 0:33

Well, that is what I said: “6. Switch over to the new file and click on the cell(s) to which you want to apply the format(s).”

– Scott
Apr 12 '13 at 0:36

This copies all formatting, though, and/or deletes existing formatting. It doesn't just copy the conditional formatting rules. I want a way to save a set of rules with a name and then apply that rule to a selection, the way you can drop down and choose a color scale, for instance.

– endolith
Sep 11 '13 at 20:17

add a comment |

You can use a template to save the all of the conditional formatting rules.

Start with a blank spreadsheet.

Add any content you want to appear in new spreadsheet.

Add the conditional formatting rules you want in each new spreadsheet.

Use Save As and choose Excel Template

When creating a new spreadsheet choose My templates and then the template you want.

template

answered Apr 11 '13 at 14:19

Brad Patton

9,181123367

But how do you save and reuse the conditional formatting rules?

– endolith
Sep 11 '13 at 20:04

@endolith: Based on your requirements (which, AFAIK, cannot be satisfied), Brad’s answer is even worse than mine. In step 4, he saves a copy of the workbook, complete with all formatting, as a template file. And in step 5 he creates a new workbook that is a copy (duplicate / clone) of the original workbook, complete with all formatting.

– Scott
Sep 11 '13 at 22:46

@Scott: Yeah I created a macro and made a button for it, which seems to be working well

– endolith
Sep 12 '13 at 14:22

add a comment |

You can use a template to save the all of the conditional formatting rules.

Start with a blank spreadsheet.

Add any content you want to appear in new spreadsheet.

Add the conditional formatting rules you want in each new spreadsheet.

Use Save As and choose Excel Template

When creating a new spreadsheet choose My templates and then the template you want.

template

answered Apr 11 '13 at 14:19

Brad Patton

9,181123367

But how do you save and reuse the conditional formatting rules?

– endolith
Sep 11 '13 at 20:04

@endolith: Based on your requirements (which, AFAIK, cannot be satisfied), Brad’s answer is even worse than mine. In step 4, he saves a copy of the workbook, complete with all formatting, as a template file. And in step 5 he creates a new workbook that is a copy (duplicate / clone) of the original workbook, complete with all formatting.

– Scott
Sep 11 '13 at 22:46

@Scott: Yeah I created a macro and made a button for it, which seems to be working well

– endolith
Sep 12 '13 at 14:22

add a comment |

You can use a template to save the all of the conditional formatting rules.

Start with a blank spreadsheet.

Add any content you want to appear in new spreadsheet.

Add the conditional formatting rules you want in each new spreadsheet.

Use Save As and choose Excel Template

When creating a new spreadsheet choose My templates and then the template you want.

template

answered Apr 11 '13 at 14:19

Brad Patton

9,181123367

You can use a template to save the all of the conditional formatting rules.

Start with a blank spreadsheet.

Add any content you want to appear in new spreadsheet.

Add the conditional formatting rules you want in each new spreadsheet.

Use Save As and choose Excel Template

When creating a new spreadsheet choose My templates and then the template you want.

template

answered Apr 11 '13 at 14:19

Brad Patton

9,181123367

answered Apr 11 '13 at 14:19

Brad Patton

9,181123367

answered Apr 11 '13 at 14:19

Brad Patton

9,181123367

answered Apr 11 '13 at 14:19

Brad Patton

9,181123367

But how do you save and reuse the conditional formatting rules?

– endolith
Sep 11 '13 at 20:04

@endolith: Based on your requirements (which, AFAIK, cannot be satisfied), Brad’s answer is even worse than mine. In step 4, he saves a copy of the workbook, complete with all formatting, as a template file. And in step 5 he creates a new workbook that is a copy (duplicate / clone) of the original workbook, complete with all formatting.

– Scott
Sep 11 '13 at 22:46

@Scott: Yeah I created a macro and made a button for it, which seems to be working well

– endolith
Sep 12 '13 at 14:22

add a comment |

But how do you save and reuse the conditional formatting rules?

– endolith
Sep 11 '13 at 20:04

@endolith: Based on your requirements (which, AFAIK, cannot be satisfied), Brad’s answer is even worse than mine. In step 4, he saves a copy of the workbook, complete with all formatting, as a template file. And in step 5 he creates a new workbook that is a copy (duplicate / clone) of the original workbook, complete with all formatting.

– Scott
Sep 11 '13 at 22:46

@Scott: Yeah I created a macro and made a button for it, which seems to be working well

– endolith
Sep 12 '13 at 14:22

But how do you save and reuse the conditional formatting rules?

– endolith
Sep 11 '13 at 20:04

@endolith: Based on your requirements (which, AFAIK, cannot be satisfied), Brad’s answer is even worse than mine. In step 4, he saves a copy of the workbook, complete with all formatting, as a template file. And in step 5 he creates a new workbook that is a copy (duplicate / clone) of the original workbook, complete with all formatting.

– Scott
Sep 11 '13 at 22:46

@Scott: Yeah I created a macro and made a button for it, which seems to be working well

– endolith
Sep 12 '13 at 14:22

add a comment |

record a macro,run it the next time you have to apply the conditional format.

answered Apr 12 '13 at 0:43

truthurt

693

I recorded a macro of applying 2 conditional formattings to a range, saved to Personal Macro Workbook, then followed these directions for making a quick button. Now I can select a range and click the button and the conditional formatting is applied. It only applies to the selected range (color-highlight extreme values), so it works correctly.

– endolith
Sep 12 '13 at 14:51

add a comment |

record a macro,run it the next time you have to apply the conditional format.

answered Apr 12 '13 at 0:43

truthurt

693

I recorded a macro of applying 2 conditional formattings to a range, saved to Personal Macro Workbook, then followed these directions for making a quick button. Now I can select a range and click the button and the conditional formatting is applied. It only applies to the selected range (color-highlight extreme values), so it works correctly.

– endolith
Sep 12 '13 at 14:51

add a comment |

record a macro,run it the next time you have to apply the conditional format.

answered Apr 12 '13 at 0:43

truthurt

693

record a macro,run it the next time you have to apply the conditional format.

answered Apr 12 '13 at 0:43

truthurt

693

answered Apr 12 '13 at 0:43

truthurt

693

answered Apr 12 '13 at 0:43

truthurt

693

answered Apr 12 '13 at 0:43

truthurt

693

I recorded a macro of applying 2 conditional formattings to a range, saved to Personal Macro Workbook, then followed these directions for making a quick button. Now I can select a range and click the button and the conditional formatting is applied. It only applies to the selected range (color-highlight extreme values), so it works correctly.

– endolith
Sep 12 '13 at 14:51

add a comment |

I recorded a macro of applying 2 conditional formattings to a range, saved to Personal Macro Workbook, then followed these directions for making a quick button. Now I can select a range and click the button and the conditional formatting is applied. It only applies to the selected range (color-highlight extreme values), so it works correctly.

– endolith
Sep 12 '13 at 14:51

I recorded a macro of applying 2 conditional formattings to a range, saved to Personal Macro Workbook, then followed these directions for making a quick button. Now I can select a range and click the button and the conditional formatting is applied. It only applies to the selected range (color-highlight extreme values), so it works correctly.

– endolith
Sep 12 '13 at 14:51

add a comment |

protected by Community♦ Feb 1 at 17:31

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ytdyklly