How to use your Office Theme as Power BI Report Theme

[DominikPetri]

With Report Themes (preview feature in Power BI Desktop) you can apply a color theme to your entire report, such as corporate colors.

Do you already have a corporate color palette in Microsoft Office (Word, PowerPoint, Excel etc.) and want to use it Power BI Desktop? I have created an Excel File that converts the Office Theme Colors into a Power BI Desktop Theme in 4 simple steps:

1. Select the Office Theme Color in Excel from the Page Layout tab
2. The embedded VBA code will create the hex codes for the color palette
3. Name your theme
4. Preview the theme and save it (via dialog) in your preferred folder

You can download the file from my OneDrive (since it contains forms and VBA you can't run it in Excel Online):

Office Theme Converter v3 as zip file

If you have any suggestions, improvements, comments etc. please let me know 🙂

Thank you and have fun converting your existing Office Design Colors to Power BI Desktop!

Dominik Petri

[Magsy2000]

Why is it, that when opened in Excel there is no Marco assigned to the Convert button?

[DominikPetri]

The macros are definitely in the file and assigned to the button. I suspect that due to your security settings the macros got disabled...

[mrothschild]

Thanks for sharing.  Worked perfectly!

[severgreen]

Newbie question - I have downloaded your file and converted my branding colors and saved as a JSON... But now what do I do with it? I know I can upload JSON files into PowerBI but I'm not sure that's the action to take. How do I actually get these colors inside my dashboard in Power BI?

[DominikPetri]

Have a look here:

https://docs.microsoft.com/en-us/power-bi/desktop-report-themes

Kind regards,

Dominik.

[bman6074]

Thanks for posting this. Worked like a charm. 

[DominikPetri]

Glad that you like it. Please give kudos to the original post 😉
Thank you! 🙂

[scavins]

This macro worked perfectly. I didn't have to get the hex codes for all the colors in our corporate theme and make my own JSON.

[SusanD]

Hi

Sorry if this turns up as a duplicate, my first attempt seems to have been lost! I've got the workbook downloaded, but as soon as I click the button to generate the hex codes, the JSON preview cell just displays #NAME? this happens whether I have changed the theme or not, even with the theme that the book came with. It starts off ok, displaying a preview, but as soon as that hex button is clicked, the preview stops working. Click into the box and you can see the formula, but no sensible output?

Thanks!

[DominikPetri]

Hi Susan,

I guess this is due to the fact that I used the TEXTJOIN function in Excel. This function is only available in the Office 365 Edition of Excel 2016.

You can download a version (zip file) here that does NOT use this function:

Office Theme Converter v2 as zip file

Hope this helps

Dominik.

[SusanD]

Great thank you, that's done the trick.

[npnigro]

Very neat, but it looks like the Hex2Dec function returns the bytes in the wrong order. I believe the hex code should be the same as the web color code. If so, it seems like the first and third bytes are reversed. 

[DominikPetri]

@npnigro your are completely right. I've already fixed this problem and will have the file changed by the admin shortly.

The VBA property RGB returns the hex code in the order BGR instead of RGB - which I didn't know but do know now 🙂

Best regards

Dominik.

[DominikPetri · Accepted solution]

Hi all,

you can download the updated version in which I fixed the hex codes bug here:

Office Theme Converter v3 as zip file

Let me know what you think!

Thanks

Dominik Petri.

[TheoC]

Although it has been some time since you posted this, the tool you built is really cool!  Well done, @DominikPetri!

[dcanalesg]

When I tried to download the Excel file from OneDrive I got the following message:

"Features that we can't show in the browser and interactive reports will be removed from the downloaded copy."

After I downloaded the file I had to insert the "Generate Hex-Codes for Colors" and "Save as JSON" buttons and assign the corresponding macros.

Because I use Excel 2013 and Textjoin is a new Excel 2016 formula I had to replace the third line of the formula on row 25 with:

CHAR(34) & "dataColors" & CHAR(34) & ": [ " & Accent_1 &","& Accent_2 &","& Accent_3 &","& Accent_4 &","& Accent_5 &","& Accent_6 &","& Hyperlink &","& Followed_Hyperlink & " ]," & CHAR(10) &

After that everything worked fine. Thanks!!!!

[dcanalesg]

When I tried to download the excel file from OneDrive I got the following message:

"Features that we can't show in the browser and interactive reports will be removed from the downloaded copy."

After I downloaded the Excel file I had to insert the "Generate Hex-Codes for Color" and the "Save as JSON" buttons, and assign the corresponding macros.

I have Excel 2013 and because Textjoin is a new Excel 2016 function I had to replace the third line of the formulas on row 25 with: 

CHAR(34) & "dataColors" & CHAR(34) & ": [ " & Accent_1 &","& Accent_2 &","& Accent_3 &","& Accent_4 &","& Accent_5 &","& Accent_6 &","& Hyperlink &","& Followed_Hyperlink & " ]," & CHAR(10) &

After that, everyting worked fine. Thanks!!!!

[DominikPetri]

Hi @dcanalesg,

Glad that you like it. If you download the file to YOUR OneDrive first, the buttons should be there.

Nevertheless, thanks for posting your solution and your formula for those who use Excel 2013 (or Excel 2016 without Office 365) and therefore don't have TEXTJOIN.

I will (hopefully) soon finish a blog post with an alternate download link....and a version without TEXTJOIN 🙂

As soon as the blog post is online, I will post the link here.

Dominik.

[LarsSchreiber]

Hey Dominik,

I really like your idea and think it's very useful. Thanks a lot. 

Regards,

Lars