Do you find yourself creating new workbooks in Excel, then making the same changes to every one? Maybe you like to change font size, zoom percent, or the default row height? If so, you can save yourself time and trouble by setting a default template for Excel to use each time you create a new workbook. As long as you name the template correctly, and put it in the correct location, Excel will use your custom template to create all new workbooks. Note: biggest challenge with this tip is figuring out the right location for the template file.
In Excel 2007 and 2010, choose Page Break Preview from the Workbook Views group on the Layout tab. Excel will prompt you with a quick reminder that you can adjust page breaks by clicking.
This can be maddeningly complex, depending on which platform and version of Excel you use. If you get frustrated and can't make things work, you can set your own startup folder manually, as described below. Settings that can be saved in a template A template can hold many custom options. Here are a few examples of settings that can saved in a workbook template:. Font formatting and styles.
Display options and zoom settings. Page setup and print options. Column widths and row heights. Page formats and print area settings for each sheet. The number (and type) of sheets in new workbooks. Placeholder text (titles, column headers, etc.). Data validation settings.
Macros, hyperlinks and ActiveX controls. Workbook calculation options Remember: these setting only apply to new workbooks created after a custom template file is installed. The process. Open a new blank workbook and customize the options as you like.
Save the workbook as an Excel template with the name ' book' (Excel will add.xltx). Move the template to the startup folder used by Excel. Quit and relaunch Excel to be sure settings are fresh. Test to be sure Excel is using the template when new workbooks are created. Based on comments below, it seems the name of your workbook must be localized for your version of Excel.
For example, if you're using the Czech version, you need to to use 'Sešit' instead of 'book'. Common startup folder locations Whenever Excel is launched, it establishes what is called a 'startup folder', which is named XLSTART. The key is to put your template file into this folder so that Excel will find it. Unfortunately, the exact location of XLSTART varies according the versions of Excel and Windows you use. Here are some common locations: C: Program Files Microsoft Office OFFICEx XLSTART C: Users user AppData Microsoft Excel XLSTART C: Users user AppData Roaming Microsoft Excel XLSTART Can't find XLSTART? If you can't find the startup folder for excel (XLSTART), you can use the VBA editor to confirm Excel's start up path:. Run Excel. Open the VBA editor (Alt + F11).
Open the immediate window (Control + G). Type:? Application.StartupPath in the window. Press Enter The startup path will appear below the command.
Once you've confirmed the location of XLSTART, drop in your template file. Set your own startup directory If you can't find Excel's startup directory, or if burying your template deep in an application hierarchy just seems wrong, you can tell Excel to look in your own startup folder by setting an option as follows:. Create a directory called ' xlstart' where you like. Put your custom template the new directory.
At Options Advanced General Open all files in, enter the path to xlstart. Test to make sure the template is working Telling Excel about your own startup folder.make sure you use the correct path on your computer! Test to make sure your template is being used After you go through the steps to set up a default template, make sure you test to confirm your template is being used. One easy way to do this is to (temporarily) give cell A1 in your template a bright yellow or orange fill. That way, you can immediately see if your custom template is being used. Once you're sure things are working, remove the marker. Setting a default Excel template on the Mac The process for setting a default Excel template on a Mac is similar to the steps above for Windows.
Again, confirming the startup folder can be tricky, depending on whether y ou have Excel 2011 or 2016 installed (2008 not tested). In Excel 2016, according to Microsoft,. Also, as of mid-2016, the name of the template should be 'workbook' (manually remove the.xltx extension) not 'book', as described in a this. (The location mentioned in this article also seems to have changed in later 2011 versions).
Part 2 of our series on Microsoft Office for Mac 2011 brings us to that wonder of spreadsheets: Excel. From doing basic math to shortening and combining pieces of data, or even just keeping track of the things you need, Excel has got you covered.
Check out the hidden tips and tricks for Microsoft Excel for Mac 2011 below to start using this amazing application for more than just banking. Most of these tips are not specific to Excel for Mac, but the instructions on how to do them are. (If you missed it, here’s.) Note: Some of the buttons referenced below are only visible in the Excel toolbar when you expand the Excel window to its full width. Excel will hide some buttons to save space when the window is made narrower. Find the Difference Between Two Dates A little-known formula in Excel is the DateDif function.
With DateDif, you can see how many days or months there are between two given dates. This can be very useful in a spreadsheet that looks at the date a bill is sent out versus when it gets paid. It’s also useful a simple one-off formula for you to quickly calculate how many days it’s been since you started your. Open Excel for Mac and enter two dates next to each other in cells A1 and B1. Next, in another cell, enter this formula: =datedif(A1,B1,“d”) This will give you the number of days between the two dates. To see the number of months, replace the “d” with “m”.
Change that to “y” and – you guessed it – you’ve got years. You could use this to see how old all of your friends and family are by having one column with their birth dates, a second column that’s auto-filled with today’s date by using the formula =today, and a third column with the year formula listed above. There are a few other arguments you can use, too. Check out for the rest. Constrain Cell Input Selection Let’s say you’re typing some data into a certain area of an Excel for Mac 2011 sheet that doesn’t line up with the top or sidebars. This means you have to use the mouse to keep clicking over to your starting point again when you go to another line.
Instead of doing this, all you have to do is highlight the area you’re working inside of. Then just hit the Tab button to move between cells. When you get to the last cell that’s selected in a row and hit Tab, you’ll be moved to the first selected cell in the next row. Show Alternating Row Colors Staring at data in Excel all day is enough to make you go cross-eyed. While that’s bad for you, it’s really bad for making sure you’re reading the data correctly. A simple solution to this is to add color to alternating rows, which helps you read the data and follow it much more clearly. While you could color each row by hand, that’s hard to do, and worst of all, the color comes with you when you copy/paste.
There’s a much easier and permanent way of coloring alternating rows. There are a few steps that you need to follow, and Microsoft lays them out perfectly on its. You can also drag a selection box around the area where you wish to alternate colors, click on the Tables tab of the Excel Ribbon, and then click New. This will give you a table that comes with alternating colors as well as other features such as sorting and totaling. Duplicate contacts killing your productivity?
See how Scrubly can help in this. Force a Line Break Within a Cell Excel isn’t known for its ability to format text, which is why when you type a long sentence or even a paragraph you just get a long, continuous line of text that’s cut off by the next cell. Instead, you can force line breaks in Excel for Mac 2011 so your text looks better and is readable.
First, on the Home tab of the Ribbon, click on Wrap Text to make sure text wraps in the cell. This will break text as the cell is sized.
To break it where you want it, hit Control + Option + Return on your keyboard to stay in the cell but start a new line. Color-Code Sheets in a Workbook If you work with multiple sheets in a single workbook, keeping track of them can get a little crazy. While you can rename them by double-clicking the title and entering a new one, sometimes this isn’t enough. Instead, you can use color to denote what each page in the workbook has in it. For example, a workbook with all your finances in it could have blue denoting credit cards while green denotes cash.
Doing this is very easy. Just right-click on the workbook page and select Tab Color. Choose a color and now that tab will be highlighted. Hide Gridlines Grids in Excel are great for lining up rows and columns. But if you’re looking for fewer lines and more wide-open space, you can actually remove the gridlines in Excel for Mac 2011 pretty easily. Go to Excel Preferences in the menu bar and then under the Authoring section click View. On the View page uncheck the box that says “Show gridlines” and click OK.
When you go back to the Excel book you’ll still have cells, but no grid lines separating them. Use Excel to Highlight and Remove Duplicates Let’s say you have an extremely long list and you need to find all the duplicates. Whether it’s names, phone numbers or duplicate contacts, Excel for Mac 2011 can make the process quick and easy. There are actually two ways of performing this operation.
For the first method, open your list inside Excel and click the column you want to look for duplicates in. Next, in the menu bar click the Format Conditional Formatting. You’ll get a new window that’s titled “Manage Rules.” Click the + at the bottom of the window and in the Style drop-down choose Classic.
In the drop-down that says “Format only top” choose Format only unique or duplicate values and click OK. Now any duplicates in the list will show up with red fill and dark red text. Click OK in both windows to close out. The only downside with this method is that all duplicates look the same, so you have to look to find where they are.
If you just want the duplicates removed, then option 2 is for you. Select the column you want to remove duplicates from and click on the Data tab of the Excel for Mac Ribbon. Next, click the Remove Duplicates button and you’ll see a review of what will be removed.
Click the Remove Duplicates button in this pop-up window to complete the process. Both of these methods will help you eliminate duplicates; the only difference is whether you want them deleted right away or you just want to be notified that they exist. The first method is great for the long term, as you’ll instantly see if a duplicate entry is added, since the Conditional Formatting runs non-stop once initiated.