Over the next few months we’re going to provide basic tips to help you save time, reduce errors, resolve common problems, and create slick looking spreadsheets.

Even if you absolutely hate Excel… we think you’ll get something out of this ūüôā

We’re using Microsoft Excel for Mac 2011, but these tips should work in all other versions.

Paste Special

Copy and Paste copies and pastes all the data from one cell to another.

Paste Special lets you choose what part of the data you paste: formulas, formats, values, comments, column widths, or 10 other data types.

  • Copy as normal.
  • In the menu bar click¬†Edit>Paste Special.¬†The keystroke shortcut is¬†Control Command V
  • Choose what you want to Paste from the dialog box.

 

Microsoft Office For Mac 2011 - Excel - Paste Special

Once you try this, you’ll find all kinds of uses for it.

Group Columns or Rows

When we need to hide columns or rows most of us right-click and use the Hide command.

Unfortunately, this makes it difficult for others to detect that our spreadsheet contains hidden data.

The solution is to stop using Hide, and start using the Group command:

  • Highlight columns or rows that you wish to group.
  • In the menu bar and click¬†Data>Group and Outline>Group…¬†The keystroke shortcut is¬†Command Shift K.

Excel-Grouping-1

On the spreadsheet above we grouped columns E through H.

Clicking the plus symbol reveals grouped columns or rows.

Excel-Grouping-3

Clicking the minus symbol hides those same columns and rows.

Excel-Grouping-2

To¬†Ungroup, highlight a grouped area then go to the menu bar and click¬†Data>Group and Outline>Ungroup…

The keystroke shortcut is Command Shift J.

Center Across Selection

When you want to center a spreadsheet title across rows, the quick option is to highlight those rows and use the Merge Cells command.

This looks perfect, but can cause problems later on. Excel cannot perform certain functions on merged cells.

You can avoid this by using Center Across Selection.

When you want to center data across rows:

  • Highlight those rows.
  • In the menu bar and select¬†Format>Cells…

Excel For Mac - Center-Highlighted

  • In the box that opens choose the Alignment tab.
  • Under the¬†Horizontal¬†drop-down choose Center Across Selection.

Excel For Mac - Format Cell PositionThe data is now centered without merging any cells.

Excel For Mac - Centered Across Selection

Currently there is no keystroke shortcut for this Command.

Hope this makes your Wednesday a little more bearable ūüôā

No Problem Mac - 310-621-5679 Blog Ad

If you have questions, please use the Comments section below.

John Carroll - No Problem Mac

Get More Answers!

Super easy tutorials for the Mac, iPhone and iPad.

Subscribe to our Newsletter.

It's literally a no-brainer!

You have Successfully Subscribed!