Home

Seeking solutions/workarounds for creating Excel Master Workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-889434%22%20slang%3D%22en-US%22%3ESeeking%20solutions%2Fworkarounds%20for%20creating%20Excel%20Master%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889434%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20find%20solutions%20for%20creating%20a%20master%20template%20in%20Excel.%20We%20have%20almost%20100%20Excel%20workbooks%20where%20data%20in%20them%20varies%20but%20the%20formatting%20and%20cell%20and%20row%20layout%20including%20header%20names%20remains%20the%20same.%20When%20a%20row%20is%20deleted%20in%20one%2C%20it%20needs%20to%20be%20deleted%20in%20all%20of%20them.%20When%20a%20row%20header%20is%20updated%2C%20it%20needs%20to%20be%20updated%20in%20all%20of%20them.%20When%20conditional%20formatting%20changes%20in%20one%2C%20it%20needs%20to%20be%20updated%20in%20all%20of%20them.%20Get%20the%20gist%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESolutions%20I've%20researched%20that%20won't%20work%3A%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3ECell%20linking%3C%2FSTRONG%3E%3CSPAN%3E%20-%20only%20copies%20the%20data%20and%20not%20the%20format%3B%20doesn't%20account%20for%20adding%2Fdeleting%20rows%2Fcolumns%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EFormat%20Paint%3C%2FSTRONG%3E%3CSPAN%3E%20-%20doesn't%20work%20well%20between%20different%20workbooks%20and%20still%20needs%20to%20be%20applied%20to%20each%20workbook%20individually%3B%20doesn't%20copy%20conditional%20formatting%3B%20doesn't%20account%20for%20adding%2Fdeleting%20rows%2Fcolumns%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EPower%20Query%3C%2FSTRONG%3E%3CSPAN%3E%20-%20formatting%20is%20based%20on%20the%20destination%20file%20and%20there's%20concern%20that%20when%20refreshed%20it%20would%20delete%2Foverwrite%20all%20of%20the%20data%20specific%20to%20that%20workbook%3B%20doesn't%20copy%20conditional%20formatting%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EFind%2Freplace%3C%2FSTRONG%3E%3CSPAN%3E%20-%20not%20ideal%20and%20doesn't%20work%20with%20exact%20match%20(when%20trying%20to%20replace%20AP%20it%20makes%20no%20distinction%20between%20AP%20or%20apply)%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CUL%3E%3CLI%3E%3CSTRONG%3EMS%20Access%3C%2FSTRONG%3E%3CSPAN%3E%20-%20for%20business%20reasons%20this%20needs%20to%20stay%20in%20Excel%20(i.e.%20can't%20use%20Access%20or%20other%20database%20programs)%3B%20this%20option%20doesn't%20address%20the%20conditional%20formatting%20applied%20to%20all%20of%20the%20workbooks%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20seen%20numerous%20threads%20from%20people%20looking%20at%20how%20to%20create%20a%20master%20template%20in%20Excel.%20I%20get%20that%20its%20not%20possible%20(and%20I%20have%20no%20idea%20why%20Microsoft%20in%20all%20its%20wisdom%20hasn't%20created%20function%20yet)%2C%20but%20what%20I'm%20looking%20for%20are%20possible%20workarounds.%20Right%20now%20the%20process%20can%20take%20a%20couple%20people%20several%20days%20to%20update%20all%20of%20the%20workbooks.%20Any%20work%20arounds%20that%20can%20reduce%20this%20to%20a%20single%20person%20in%20just%20a%20few%20hours%20would%20be%20fantastic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-889434%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-901366%22%20slang%3D%22en-US%22%3ERe%3A%20Seeking%20solutions%2Fworkarounds%20for%20creating%20Excel%20Master%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901366%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418958%22%20target%3D%22_blank%22%3E%40anahka_7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understand%20correctly%20you%20should%20just%20be%20able%20to%20select%20all%20worksheets%20you%20want%20to%20change%20and%20then%20make%20the%20change.%20Show%20this%20video%20where%20I%20show%20what%20I%20mean%20for%20the%20header%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fkteamch-my.sharepoint.com%2F%3Av%3A%2Fg%2Fpersonal%2Fpascal_kiefer_kteam_ch%2FEbxHRcUxqEFNrgdZV2zJEEQB6-hdA00X_R9lfqtUWqqAZg%3Fe%3DGnMEqk%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fkteamch-my.sharepoint.com%2F%3Av%3A%2Fg%2Fpersonal%2Fpascal_kiefer_kteam_ch%2FEbxHRcUxqEFNrgdZV2zJEEQB6-hdA00X_R9lfqtUWqqAZg%3Fe%3DGnMEqk%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-901979%22%20slang%3D%22en-US%22%3ERe%3A%20Seeking%20solutions%2Fworkarounds%20for%20creating%20Excel%20Master%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20get%20the%20video%20to%20load%2C%20but%20I%20need%20a%20solution%20for%20workbooks%2C%20not%20worksheets.%20We're%20working%20with%20around%20100%20workbooks%20and%20each%20of%20those%20have%20about%2010%20different%20worksheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E
anahka_7
New Contributor

I'm trying to find solutions for creating a master template in Excel. We have almost 100 Excel workbooks where data in them varies but the formatting and cell and row layout including header names remains the same. When a row is deleted in one, it needs to be deleted in all of them. When a row header is updated, it needs to be updated in all of them. When conditional formatting changes in one, it needs to be updated in all of them. Get the gist?

 

Solutions I've researched that won't work:

  • Cell linking - only copies the data and not the format; doesn't account for adding/deleting rows/columns
  • Format Paint - doesn't work well between different workbooks and still needs to be applied to each workbook individually; doesn't copy conditional formatting; doesn't account for adding/deleting rows/columns
  • Power Query - formatting is based on the destination file and there's concern that when refreshed it would delete/overwrite all of the data specific to that workbook; doesn't copy conditional formatting
  • Find/replace - not ideal and doesn't work with exact match (when trying to replace AP it makes no distinction between AP or apply)
  • MS Access - for business reasons this needs to stay in Excel (i.e. can't use Access or other database programs); this option doesn't address the conditional formatting applied to all of the workbooks

 

I've seen numerous threads from people looking at how to create a master template in Excel. I get that its not possible (and I have no idea why Microsoft in all its wisdom hasn't created function yet), but what I'm looking for are possible workarounds. Right now the process can take a couple people several days to update all of the workbooks. Any work arounds that can reduce this to a single person in just a few hours would be fantastic.

3 Replies

@anahka_7 

 

If I understand correctly you should just be able to select all worksheets you want to change and then make the change. Show this video where I show what I mean for the header

 

https://kteamch-my.sharepoint.com/:v:/g/personal/pascal_kiefer_kteam_ch/EbxHRcUxqEFNrgdZV2zJEEQB6-hd...

@PascalKTeam 

I can't get the video to load, but I need a solution for workbooks, not worksheets. We're working with around 100 workbooks and each of those have about 10 different worksheets.

@anahka_7 

 

Oh sorry I didn't see that you were talking about multiple workbooks. Well that will be tricky I guess, I don't know any solution for that. Hopefully somebody else can help out.

My solution was about multiple worksheets within one workbook (I uploaded the video to dropbox this time, maybe that works better: https://www.dropbox.com/s/ogh988aom3dosok/master%20workbook.mp4?dl=0)

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies