Office for Mac: Hidden Tips & Tricks in Microsoft Excel for Mac 2011

Microsoft Excel for Mac 2011 logo superimposed on blackboard filled with numbers

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 our post on tips and tricks in Word for Mac.)

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.

SEE ALSO: How to Sync Google Contacts with the iPhone

1. 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 good habits.

Open Excel for Mac and enter two dates next to each other in cells A1 and B1. Next, in another cell, enter this formula:


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.

Microsoft Excel for Mac 2011 spreadsheet showing example of DateDif formula

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 this site out for the rest.

SEE ALSO: How to Get Rid of Outlook Duplicate Contacts

2. 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.

Excel for Mac sheet showing constrained cell input selection

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.

3. 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 help documentation.

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 100-second video.

4. 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.

5. 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.

Before and After of color-coded Excel worksheet tab

6. 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.

Excel Preferences window

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.

Excel for Mac worksheet without gridlines

RELATED: The Productivity of Mark Twain

7. 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.

1. 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.”

Excel for Mac Manage Rules window

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.

New Formatting Rule window in Excel for Mac

Now any duplicates in the list will show up with red fill and dark red text. Click OK in both windows to close out.

Closeup of duplicates highlight in Excel for Mac worksheet

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.

2. 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.

Remove Duplicates pop-up window in Excel for Mac

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.

Scrubly social-sharing menu.

Scrubly sign-up.