Categories
Device

How to save time with excel themes

If you often use the same layout or data in a workbook, save it as a template so you can use the template to create more workbooks instead of starting from scratch.

You can use one of your own templates to create a new workbook, or you can use one of the many predefined templates that you can download from Microsoft Office Online.

Save a workbook as a template

If you’re saving a workbook to a template for the first time, start by setting the default personal templates location :

Click File > Options.

Click Save, and then under Save workbooks, enter the path to the personal templates location in the Default personal templates location box.

This path is typically: C:\Users\[UserName]\Documents\Custom Office Templates.

How to save time with excel themes

Once this option is set, all custom templates you save to the My Templates folder automatically appear under Personal on the New page ( File > New).

Open the workbook you want to use as a template.

Click File > Export.

Under Export, click Change File Type.

In the Workbook File Types box, double-click Template.

In the File name box, type the name you want to use for the template.

Click Save, and then close the template.

Create a workbook based on the template

With the template you created above, you can create a new workbook. This section tells you how to do that.

Note: Alternately, you can browse for various online templates in Excel and use them instead. For more information, see Download free, pre-built templates.

Click File > New.

How to save time with excel themes

Double-click the template you just created.

Excel creates a new workbook that is based on your template.

Save a workbook as a template

Open the workbook that you want to use as a template.

Click File, and then click Save As.

In the File name box, type the name that you want to use for the template.

In the Save as type box, click Excel Template, or click Excel Macro-Enabled Template if the workbook contains macros that you want to make available in the template.

The template is automatically placed in the Templates folder to ensure that it will be available when you want to use it to create a new workbook.

Tip: You can copy any Excel workbook into the Templates folder, then you can use the workbook as a template without saving it in the template file format (.xltx or .xltm). In Windows Vista, the Templates folder is usually C:\Users\ \AppData\Roaming\Microsoft\Templates. In Microsoft Windows XP, the Templates folder is usually C:\Documents and Settings\ \Application Data\Microsoft\Templates.

Use a template to create a new workbook

Click File and then click New.

Under Available Templates, do one of the following:

To base the workbook on a recently used template, click Recent Templates, and then double-click the template that you want to use.

To base the workbook on an installed template, click Sample templates and then double-click the template that you want to use.

To base the workbook on a template that you created, click My templates, and then on the Personal Templates tab, double-click the template that you want to use.

To base the workbook on another workbook, click New from Existing, and then double-click the workbook that you want to use.

Tip: When you are connected to the Internet, you can also access templates that are available from Office.com. Under Office.Com Templates, click a template category, and then under that category, double-click the template that you want to download. If the template that you want to download has been supplied by a member of the Microsoft Office.com Community, you must click I accept to accept the License Agreement for Community Templates Terms of Use before you can download that template.

Save a workbook as a template

Open the workbook that you want to use as a template.

Click the Microsoft Office Button , and then click Save As.

In the File name box, type the name that you want to use for the template.

In the Save as type box, click Excel Template, or click Excel Macro-Enabled Template if the workbook contains macros that you want to make available in the template.

The template is automatically placed in the Templates folder to ensure that it will be available when you want to use it to create a new workbook.

Tip: You can copy any Excel workbook into the Templates folder, then you can use the workbook as a template without saving it in the template file format (.xltx or .xltm). In Windows Vista, the Templates folder is usually C:\Users\ \AppData\Roaming\Microsoft\Templates. In Microsoft Windows XP, the Templates folder is usually C:\Documents and Settings\ \Application Data\Microsoft\Templates.

Use a template to create a new workbook

Click the Microsoft Office Button , and then click New.

Under Templates, do one of the following:

To base the workbook on a recently used template, click Blank and recent, and then under Recently Used Templates, double-click the template that you want to use.

To base the workbook on an installed template, click Installed Templates, and then under Installed Templates, double-click the template that you want to use.

To base the workbook on a template that you created, click My templates, and then on the My Templates tab, double-click the template that you want to use.

To base the workbook on another workbook, click New from Existing, and then double-click the workbook that you want to use.

Tip: When you are connected to the Internet, you can also access templates that are available from Office Online. Under Microsoft Office Online, click a template category, and then under that category, double-click the template that you want to download. If the template that you want to download has been supplied by a member of the Microsoft Office Online Community, you must click I accept to accept the License Agreement for Community Templates Terms of Use before you can download that template.

Want to be a more efficient — and effective — user of Microsoft’s spreadsheet application? These tips can do the trick.

If you are an accountant, you almost certainly use Microsoft Excel.

If you’re like most Excel users, you almost certainly don’t take full advantage of the application’s numerous time-saving features.

That’s OK. Not everybody can be an Excel savant — but almost everybody can get better.

This article features a quartet of Excel tricks to make you more efficient — and effective — with the world’s most popular spreadsheet application. Each tip includes a video walkthrough. You can also download an Excel file with separate worksheets for each trick.

Note: The content of this article is based on Microsoft Excel 2016 for PCs. Other versions of Excel may work differently. More details on other Excel versions can be found by searching for the version (e.g., “Excel 2010” or “Excel for Office 365”) at support.office.com.

How to quickly foot and cross-foot in Excel

Many accountants consider it good practice to foot and cross-foot a table of values. This means totaling the values of each column (foot) and then verifying the accuracy of those totals by summing together all foots and comparing it to the sum of all rows (cross-foot). Fortunately, Excel can do this in two quick steps.

AutoSum can be used to insert sum formulas that total all columns and rows at the same time. Highlight the data, plus one additional row below and one additional column to the right of the data. On the Home tab, click the AutoSum button, or press Alt + = (equal sign).

@Computergaga1
Jun 27, 2019, 11:23 am EST | 2 min read

How to save time with excel themes

An Excel theme is a collection of colors, fonts, and effects that you can apply to a workbook with a couple of clicks. Themes ensure a consistent and professional look to your reports, and they let you more easily adhere to company branding and identity guidelines.

Applying an Excel Theme

You will find the themes under the “Page Layout” tab of Excel.

How to save time with excel themes

You can select a theme for your workbook, which would apply the collection of colors, fonts, and shape effects.

How to save time with excel themes

Or, you could select just a specific color or font theme to apply.

How to save time with excel themes

Excel features many built-in themes (and also color and font themes) that you can apply to a workbook for visual impact, saving you time trying to choose colors and fonts by hand that would work well together.

The image below shows the Blue Green color theme applied to a workbook. Both charts adopt the color theme, keeping a consistent look and feel.

How to save time with excel themes

How to Create a Custom Theme

This is great! But the ability to create a custom theme is where the magic is.

Create a Custom Color Theme

Click on the “Colors” list in the Themes group and click “Customize Colors” from the bottom of the list.

How to save time with excel themes

This will open the Create New Theme Colors window.

Enter a name for your new color theme. In this example, I am creating a color theme for the marketing team.

You then select the colors you would like available in this theme from the lists and click “Save” when you’re done.

How to save time with excel themes

This color theme is then available from the list of options to apply to the workbook.

The image below shows the theme applied and its impact on the charts and also to the cells behind the list.

How to save time with excel themes

The applied theme will also affect the options you get when applying colors in other areas of Excel. For example, you can see the color selection now available when applying fill color.

How to save time with excel themes

The options are variations of the colors I selected in the Marketing theme.

Create a Custom Font Theme

Click the “Fonts” button and click “Customize Fonts.”

How to save time with excel themes

The Edit Theme Fonts window opens.

Enter a name for your font theme and select the fonts you want to use for the “Heading font” and the “Body font” from the lists. Click “Save.”

How to save time with excel themes

This font theme is then available in the “Fonts” list and is applied to the workbook below.

How to save time with excel themes

The Effects Theme

The effects theme is a selection of shadows, reflections, lines, and other effects that can be applied to shapes.

You cannot customize these themes, but you can select one from the list provided by the “Effects” button.

How to save time with excel themes

How to Save your Custom Theme

With the custom color and font themes created and applied, they can be saved together into one theme.

Click the “Themes” button and click “Save Current Theme.”

How to save time with excel themes

The Save Current Theme window opens. A theme is a THMX file and can be saved just like an Excel workbook.

Enter a file name for the theme.

The default location is in the templates folder. Excel automatically looks here for the themes, so it is encouraged to save it here; however, you can save a theme anywhere. Click “Save.”

How to save time with excel themes

The theme can now be applied to any workbook from the “Themes” button.

How to save time with excel themes

If you saved the theme somewhere else, and not in the templates folder, click “Browse for Themes” to locate it.

With the click of a button, all the colors, fonts and effects are applied to the workbook. This can save hours of formatting time.

How to save time with excel themes

An Excel theme is a collection of colors, fonts, and effects that you can apply to a workbook with a couple of clicks. Themes ensure a consistent and professional look to your reports, and they let you more easily adhere to company branding and identity guidelines.

Applying an Excel Theme

You will find the themes under the “Page Layout” tab of Excel.

How to save time with excel themes

You can select a theme for your workbook, which would apply the collection of colors, fonts, and shape effects.

How to save time with excel themes

Or, you could select just a specific color or font theme to apply.

How to save time with excel themes

Excel features many built-in themes (and also color and font themes) that you can apply to a workbook for visual impact, saving you time trying to choose colors and fonts by hand that would work well together.

The image below shows the Blue Green color theme applied to a workbook. Both charts adopt the color theme, keeping a consistent look and feel.

How to save time with excel themes

How to Create a Custom Theme

This is great! But the ability to create a custom theme is where the magic is.

Create a Custom Color Theme

Click on the “Colors” list in the Themes group and click “Customize Colors” from the bottom of the list.

How to save time with excel themes

This will open the Create New Theme Colors window.

Enter a name for your new color theme. In this example, I am creating a color theme for the marketing team.

You then select the colors you would like available in this theme from the lists and click “Save” when you’re done.

How to save time with excel themes

This color theme is then available from the list of options to apply to the workbook.

The image below shows the theme applied and its impact on the charts and also to the cells behind the list.

How to save time with excel themes

The applied theme will also affect the options you get when applying colors in other areas of Excel. For example, you can see the color selection now available when applying fill color.

How to save time with excel themes

The options are variations of the colors I selected in the Marketing theme.

Create a Custom Font Theme

Click the “Fonts” button and click “Customize Fonts.”

How to save time with excel themes

The Edit Theme Fonts window opens.

Enter a name for your font theme and select the fonts you want to use for the “Heading font” and the “Body font” from the lists. Click “Save.”

How to save time with excel themes

This font theme is then available in the “Fonts” list and is applied to the workbook below.

How to save time with excel themes

The Effects Theme

The effects theme is a selection of shadows, reflections, lines, and other effects that can be applied to shapes.

You cannot customize these themes, but you can select one from the list provided by the “Effects” button.

How to save time with excel themes

How to Save your Custom Theme

With the custom color and font themes created and applied, they can be saved together into one theme.

Click the “Themes” button and click “Save Current Theme.”

How to save time with excel themes

The Save Current Theme window opens. A theme is a THMX file and can be saved just like an Excel workbook.

Enter a file name for the theme.

The default location is in the templates folder. Excel automatically looks here for the themes, so it is encouraged to save it here; however, you can save a theme anywhere. Click “Save.”

The theme can now be applied to any workbook from the “Themes” button.

How to save time with excel themes

If you saved the theme somewhere else, and not in the templates folder, click “Browse for Themes” to locate it.

With the click of a button, all the colors, fonts and effects are applied to the workbook. This can save hours of formatting time.

You’ve upgraded to a new version of Excel, and realize you’ll be sharing workbooks with people who haven’t upgraded yet. Running the Compatibility Checker can help you identify features or information that might not be available in an earlier version, so you can resolve some of those issues before you share your workbook.

Save a file to .xls format and review Compatibility Checker issues

Follow these steps:

Click File > Export > Change File Type.

How to save time with excel themes

How to save time with excel themes

Under Workbook File Types, double-click Excel 97-2003 Workbook (*.xls).

How to save time with excel themes

In the Save As popup window, choose a folder location for the workbook.

In the File name box, type a new file name (or use the one that’s already there).

If the Compatibility Checker appears, review any compatibility issues that were found.

How to save time with excel themes

The Find link takes you to that place in your worksheet, and the Help link takes you to information on the issue and possible solutions.

In your new version of Excel, the workbook you just saved in .xls format opens in Compatibility Mode. Keep working in this mode when you plan to send this workbook back and forth to people who open it in the earlier version of Excel.

When you no longer need backward compatibility, click File > Info > Convert to convert the workbook to the current file format so you can take advantage of What’s new in Excel.

Run the Compatibility Checker for Excel 2007 and later

To verify that a workbook is compatible with Excel 2013, 2010, or 2007, run the Compatibility Checker manually the first time you save your workbook. Then set the Compatibility Checker to run automatically every time you save that workbook.

Follow these steps:

Click File > Info > Check for Issues.

How to save time with excel themes

Choose Check Compatibility.

To check for compatibility automatically from now on, check the Check compatibility when saving this workbook box.

Tip: You can also specify the versions of Excel that you want to include when you check for compatibility. All versions are checked by default, so just uncheck the versions you don’t want to include.

How to save time with excel themes

If any issues are found, the Find link takes you to that place in your worksheet, and the Help link takes you to information on the issue and possible solutions.

Use Access to combine data sources for Data Features that use multiple tables

Note: You can also use the VLOOKUP function in Excel to address this issue, but the process is somewhat complicated, and becomes more complicated as the number of data sources to combine increases.

Excel 2010 and earlier versions don’t support data features that analyze multiple tables, because those Excel versions don’t have a Data Model. If your workbook contains any such features, you may be able to prepare them for use in earlier versions by combining the feature’s data sources into a single table and then using that new table as the data source.

One way to do this is to use Access to combine the data sources and reproduce them as a query, which you can then import into your workbook. The process goes like this:

In Access, import each original data source as a new table.

In Excel, open the Relationships dialog box by clicking Data > Data Tools > Relationships and note the relationships between the original data sources.

In Access, create a query that contains all the fields your Excel data feature needs.

In Excel, import the query into a new worksheet as a table.

Once your data is in one table in Excel, choose that table as the data source for the data features that were using multiple tables, or recreate the data features and choose the new table as their data source.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Word Tips
  • Outlook Tips

How to save file with date and time in Excel?

When we work on a workbook, sometimes, we need to add the current date and time when we save the file. Maybe, you can type the date and time after the file name into the Save As dialog, but, here, I can introduce you an easy trick to auto deal with this job.

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Save Excel file with date and time by using VBA code

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Except typing the date and time manually, the following VBA code can help you to add the current date and time to the filename automatically. Please do with following steps:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Auto add date and time to file name

3. And then press F5 key to run this code, the current date and time has been added after your active workbook name, see screenshot:

How to save time with excel themes

Note: In the above code, you need to change the file path and name to your need.

@Marshall_G08
May 2, 2019, 11:23 am EST | 1 min read

How to save time with excel themes

If you’re getting bored of the default theme in your Office desktop apps, you can change the color and background to give it a more personalized feel. It’s simple and only requires a few simple steps, so let’s get to it.

Changing the Office Color Theme via App Options

By default, Office uses what it calls the “Colorful” theme. This theme gives the title bar the color of its respective app. For example, Word’s is blue, Excel’s is green, PowerPoint’s is red, and so on. Additionally, it gives you a standard light gray background and ribbon, and a white content area.

You can change the theme from inside any Office application, and those changes are made throughout all of your Office apps.

First, open any Office application. We’ll be using Word in this example.

Click the “File” tab at the top-left of the ribbon.

How to save time with excel themes

A pane will appear on the left side with several different options. At the very bottom of the pane, select “Options.”

How to save time with excel themes

In the Word Options window that appears, make sure the “General” tab selected on the left and then on the right, find the “Personalize your copy of Microsoft Office” section. Here, you’ll be able to change your Office background and theme.

How to save time with excel themes

You have four theme options: Colorful, Dark Gray, Black, or White. Select the theme you’d like to use.

How to save time with excel themes

Next, if you want to change the Office background, you have a few selections to choose from:

  • No Background (Default)
  • Calligraphy
  • Circles and Stripes
  • Circuit
  • Clouds
  • Doodle Circles
  • Doodle Diamonds
  • Geometry
  • Lunchbox
  • School Supplies
  • Spring
  • Stars
  • Straws
  • Tree Rings
  • Underwater

How to save time with excel themes

Once you’ve selected your desired theme, click “OK” and you’re done!

Unfortunately, Microsoft didn’t provide a preview of the themes with this option, so you’ll have to repeat these steps to view each one. If you’re not happy with that and you use Office 365, you can use “Account Settings” to change the theme, which does show a preview.

Changing the Office Color Theme via Account Settings in Office 365

Back in whatever app you’re using, select “File” and then select “Account” in the pane that appears.

How to save time with excel themes

Your Account Information will appear. Here, select the menu under “Office Background” or “Office Theme,” browse through the different background and themes with a live preview, and choose the ones you want.

By default Excel files will be locked by the user that has it open.
Other users that try to open the same file will just be able to do it in read-only mode until the first user has closed it.

This can be a nice feature, but sometimes you want multiple users to be able to edit the same file at the same time.
This guide will show you how that can be done.
This how-to is based on Microsoft Excel 2016, but the same steps should also apply to Excel 2010, Excel 2013 and Excel 2019.

Depersonalize file properties

  • First create a new, or open an existing excel workbook.
    Save it in a directory where all the users that needs access has access to it.
  • Open the file and go til File ->Options.
  • Click on Trust Center in the left menu and then click Trust Center Settings….

How to save time with excel themes

  • Trust Center will appear in a new window, click on Privacy Options and uncheck Remove Personal information from file properties on save.
  • Click OK, OK.

Share Workbook

  • In Excel click the Review tab and then click Share Workbook.
    If the Share Workbook button does not appear see Adding the Share Workbook button further down in this article
  • The Share Workbook window will pop up.
    Check off Allow changes by more then one user at the same time. This also allows workbook merging.
  • Click OK.
  • A new popup with the text This action will now save the workbook. Do you want to continue? appears, click OK.
  • In top of Excel the window title will now be [Shared] – Excel.

By default the file will just update when saving, you will get a warning to resolve any conflict that might exist if two users have changed the same field.

How to save time with excel themesOthers have also made changes, but no conflicts have occurred How to save time with excel themesConflicts detected, choose which change should apply

If you want to change the update or conflict settings, click the Review tab and then Share Workbook. Se the tab Advanced for these settings.

How to save time with excel themesShare Workbook – Advanced tab

Adding the Share Workbook button

If the Share Workbook button don’t appear under the Review tab you need to add it.

  • Go til File ->Options.
  • Click on Quick Access Toolbar in the left menu.
  • Select All Commands under the Choose commands from drop-down menu
  • Select Share Workbook… and click Add > >.
  • Click OK.

How to save time with excel themesQuick Access Toolbar customization window

When you first started Excel, you saw the start screen with all the different types of templates pictured:

How to save time with excel themes

When you use a template, all you have to do is replace the information in the worksheet with your own. They help you save oodles of time and keep your worksheets consistent. In Excel 2016, you can even edit the templates or create your own and then use them again and again.

Changing the Default Workbook Template

Whenever you open a blank workbook in Excel, you are opening a template–one in which the margins, font, and themes were already defined. This is the default template. It’s not as fancy as some of the templates you see pictured, but it’s still a template. You can edit this template in the same way you’d edit the fancier templates, then save it as a new template.

To create a new template, open a workbook (or create a workbook) that you want to use as a template.

Everything you see or add to the workbook will be part of the template.

When you have the workbook the way you want your template to be, click File, then Save As.

Select the location to save the workbook to, then choose to save the workbook as an Excel Template.

How to save time with excel themes

Now whenever you open the template, all you have to do is customize it for a new workbook, then save the new workbook.

Opening an Existing Template

Microsoft provides a number of templates to make your work easier. If you already have Excel 2016 open, you can view the templates by clicking the File tab, then going to New. You can then see the templates just as you did on the start screen.

How to save time with excel themes

As you can see, the blank workbook template appears first, followed by a tour of Excel 2016. After this, you can see all the templates.

Choose the template that you want to use by clicking on it.

How to save time with excel themes

Click the Create button to create the template.

We’ve chosen the Academic calendar (any year) template, and we clicked Create.

Excel loads the template for you.

How to save time with excel themes

Search for a Template

Excel 2016 gives you a few ways to search for a template you want. At the top of the screen (File>New), you’ll see this:

How to save time with excel themes

You can type in the kind of template you want. Let’s say we want a calendar.

Type in calendar.

Excel then searches its online database for a calendar template, then shows you the results:

How to save time with excel themes

To the right, you’ll see the category pane where you can search for a calendar template by category. Simply click on a category to see its calendars.

Modifying a Template

Let’s go back to our calendar template that we created earlier:

How to save time with excel themes

Once you’ve opened a template, you can now make changes to it. You can change the font size, font color, etc. if you want, and you can also customize the data.

To edit the data, click on a cell. It then selects the cell, as shown below.

How to save time with excel themes

Now simply type the data you want.

We can also change the style of the template.

Notice below that we’ve clicked on the cell for Friday.

How to save time with excel themes

While the cell is selected, you can also click the Cell Styles button to see the style gallery.

How to save time with excel themes

Click on any item in a template, and you’ll see a bounding box appear around it:

How to save time with excel themes

You can then cut or copy it or even replace it with some other item, including an image.

If you want to change the look and feel of the tables used in the template, you can do that by selecting a table in the template, then using the Format as Table button.