Excel Shortcuts, Tips & Tricks

After receiving positive feedback regarding our Microsoft Word Tips & Shortcuts, we decided to take a deeper look at another frequently required, and often under-used software: Microsoft Excel.  

Microsoft Excel is a surprisingly powerful software that has grown significantly in its capabilities over the last two decades. For many users, it is effectively digital grid paper that can do some “autosum” math. Whether you leverage pivot tables to drive dynamic and robust reporting or rely on the ever-popular “=SUM()” function,  we decided to share some shortcuts, tips and tricks you can use to increase your efficiency while using the software.

Excel keyboard shortcuts

To do this

Press

Close a workbook

Ctrl+W

Open a workbook

Ctrl+O

Go to the Home tab

Alt+H

Save a workbook

Ctrl+S

Copy

Ctrl+C

Paste

Ctrl+V

Undo

Ctrl+Z

Remove cell contents

Delete

Choose a fill color

Alt+H, H

Cut

Ctrl+X

Go to Insert tab

Alt+N

Bold

Ctrl+B

Center align cell contents

Alt+H, A, C

Go to Page Layout tab

Alt+P

Go to Data tab

Alt+A

Go to View tab

Alt+W

Open context menu

Shift+F10, or Context key

Add borders

Alt+H, B

Delete column

Alt+H, D, C

Go to Formula tab

Alt+M

Hide the selected rows

Ctrl+9

Hide the selected columns

Ctrl+0

How to wrap text

Having text bleed over into nearby cells can be a real eyesore when working with a lot of data. The simple solution to this problem is to wrap the text so that it is distributed on multiple lines.

Below are the steps to wrap text.

  1. Select the cell with the data that needs to be wrapped.

Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips1

  1. While the cell is highlighted, select the Home tab > Wrap Text

Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips2

Your cell should now look like the example below.

Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips3

Resizing all columns and rows

When dealing with a lot of data, it can be easy to overlook the importance of a clean format.

Often data will show up with different cell sizes like the example below. 

Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips4

To correct this issue and format the columns and rows to the most optimal dimensions:

  1. Click the upper left-hand corner arrow of the sheet until all cells are highlighted
  2. Click in-between any of the columns or rows to adjust the dimensions of every cellExcel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips5

Copying data/autofill

Having to copy and paste data throughout a column can be a time-consuming task. An easier way than the traditional method is to drag the data down a column.

This method can use one of two functions: Exact copy of the data or sequential autofill.

Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips6 Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips7 Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips8

                            

To accomplish this, select the bottom right-hand corner of the cell that has the last data entry

  1. To have the autofill sequentially add values, drag down to the last cell you want to be filled.
  2. To have the autofill copy and paste the last value, hold the control key while dragging down to the last cell you want to be filled.

Transpose data

Have you ever typed an entire data set only to realize it would be better formatted as a row rather than a column? Transposing is the simple answer to this dilemma to save you time from having to re-enter all of that data.

Below are the steps to transpose columns to rows and rows to columns.

  1. Select the data set you would like to transpose
  2. Copy the data set by right-clicking > Copy

Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips9

Right-click an empty cell you would like to be transposed.

  1. Select Paste Special > Transpose
Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips10 Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips11
  1. Your data set should now look like the example below

Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips12

Pivot tables

A useful way to calculate, summarize and analyze data is through pivot tables. While this is an advanced function of Excel, below are the steps to create a table to help see comparisons, patterns and trends in your data.

For an in-depth look at how to best use a pivot table, go to the Easy Excel website

How to create a PivotTable

Select the cells from which you want to create a PivotTable.

Note: Your data shouldn't have any empty rows or columns. It must have only a single-row heading.

  1. Select Insert > PivotTable.

       Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips13

  1. Under “Choose the data that you want to analyze,” click the radio button to the left of Select a table or range. See screen capture below.

       Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips14

  1. In Table/Range, verify the cell range.
  2. Under “Choose where you want the PivotTable report to be placed,” select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear.
  3. Select OK.

How to build a PivotTable

  1. To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.

Note: Selected fields are added to their default areas: non-numeric fields are added to Rows, date and time hierarchies are added to Columns and numeric fields are added to Values.

Excel Shortcuts, Tips & Tricks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - excel-tips15

  1. To move a field from one area to another, drag the field to the target area.

For questions or future article suggestions, please send an email to servicerequest@amdataservice.com.

Related information

Connect with us!

 5 Tips To Prevent Ransomware Attacks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - profile 5 Tips To Prevent Ransomware Attacks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - facebook 5 Tips To Prevent Ransomware Attacks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - linkedin  5 Tips To Prevent Ransomware Attacks - Detroit IT Blog: Business IT Support & Network Services | AM Data Service - twitter

What is M-Files?

What is M-files? M-Files is a passionate, dynamic company providing intelligent information management solutions that are disrupting the ECM market. The M-Files solution is built on three pillars: it's metadata-based, repository neutral,...

Windows Leak Puts Computers, Servers At Risk

Windows XP and Windows Server 2003 source code was leaked recently. This means devices run on XP and Server 2003 operating systems are now even more vulnerable to attack. Microsoft hasn't supported these outdated operating systems since...

Multi-Factor Authentication (MFA) Basics

You’ve likely heard of multi-factor authentication (MFA) but what exactly is it? What does it do? Why is it important? We answer all of these questions in this blog post. We’re offering enough MFA knowledge to hold your own TED...

Backup Basics

Backups are one of the most critical components of any IT infrastructure. It’s important for any organization to back up data so their data can be restored after accidental deletion or critical failure.

(Pass)words Matter

For World Password Day, we are sharing tips, best practices and information to help you create more secure passwords and understand why this is important.
Page: 123 - All