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.
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.
On the spreadsheet above we grouped columns E through H.
Clicking the plus symbol reveals grouped columns or rows.
Clicking the minus symbol hides those same columns and rows.
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…
- In the box that opens choose the Alignment tab.
- Under the Horizontal drop-down choose Center Across Selection.
The data is now centered without merging any cells.
Currently there is no keystroke shortcut for this Command.
Hope this makes your Wednesday a little more bearable 🙂
If you have questions, please use the Comments section below.
