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
- 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.
2. While the cell is highlighted, select the Home tab > Wrap Text
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. 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 cell
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.
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
3. Right-click an empty cell you would like to be transposed.
4. Select Paste Special > Transpose
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.
2. Under “Choose the data that you want to analyze,” click the radio button to the left of Select a table or range.
3. In Table/Range, verify the cell range.
4. 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.
5. 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.
2. 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.