All Using Source Theme - Paste Special in Excel - Mystery Solved - MS Excel Paste Special

All Using Source Theme Paste Special

All Using Source Theme Paste Special

Hello everyone, welcome to HBN Infotech Tutorials. In this tutorial I am going to resolve the mystery of paste special. In paste special there is an option called “All using source theme”. For a long time this option could have startled most of you and many of you don’t really know about this option. When you try to use this option nothing seems to happen. Now let us understand the purpose, see how this option works and learn when exactly one should use this option.

If you want to see the tutorial in action you may watch the 3 minutes video tutorial below:

All Using Source Theme Paste Special – Short Explanation

This option copies all contents along with the source theme formatting from one file to another. Practically speaking this option can be used only when you are working with multiple excel files that has different theme formatting.

Now, a big question: What is a Theme?

A Theme is a combination of many formatting options put together. a theme defines the appearance of all elements such as text, shapes, smartart graphics, chart etc. by appearance i mean the options such as font color, font size, shape outline, shape fill, color of chart elements etc. you can change the appearance of all the elements on your worksheet with just one simple click. for example if you go to page layout, themes and choose a theme, you could notice that it impacts the appearance of all the text and objects with just one click.

So when you copy data and objects from another workbook that has a different theme other than the theme in your current file and do a normal copy and paste, you see that only the contents are copied, and not the formatting. Hence you should use the option “All using source theme” to paste all elements along with its formatting.

How to access the Themes option?

If you go to Page Layout Tab you can see an option called themes (Refer to the image below). And when you click on themes you could see a lot of predefined themes. “Office” is the default theme in most of the excel versions, that is Excel 2007 and later versions. If you make a single click on any of these themes, it changes the appearance of all elements such as texts, shapes and charts.

MS Excel Paste Special - All Using Source Theme

Source Theme or Default Theme

In my opinion the source theme is nothing but the default theme. When you try to copy items from different files that has the default themes set, you don’t get the same appearance. It is at this point you need to use the option “All Using Source Theme”. Apart from themes there are a lot of custom made templates that has a varied formatting and appearance.

Practical demonstration video

In the above video I have clearly shown the demonstration. I have taken 2 workbooks: Book 1 and Book 2. In Book 1 there is some data, shapes and a chart with some formatting. Now if we copy and paste them into book 2 using Ctrl+C and Ctrl+V (normal copy and paste), you can notice that all contents have been pasted except the formatting. Now this where you use the option “All using source theme”. When you use this option you can observe that all text contents including the formatting is pasted. But unfortunately this option doesn’t work with shapes, charts & other graphics.

I hope that you understood this concept. I also hope that this tutorial helped you understand what are themes and how to use them. If you have any questions you may let me know in the comments below. Thank you for visiting my website. I’ll catch you all in my next article. Until then its bye from

Similar Tutorials:

Excel VBA Input box tutorial

5 Ways to Hide Data in Excel

Excel YouTube channels List

Huge List of Free Excel learning sites

18 Advanced Excel Tips and Tricks

Excel formula to generate Cumulative calculation by drop down

Latest posts by himaghiri (see all)


Himaghiri Thanayan.N is the founder of HBN Infotech. He has great dreams and passion for Graphic Design, Web Design, CAD Design, Arts & Crafts, Science & Information Technology and Article Writing.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.