Microsoft Excel is a great program to do numerical analysis. However, it can be tedious to use. There are many processes and functions that are time consuming to repeat over and over. Learning Excel tips and tricks can vastly improve work efficiency. Read on to discover some essential Excel hacks to make your life easier.
Shortcuts to Navigate More Smoothly
A few key shortcuts can save a great deal of time navigating around Excel.
- Move to the last cell in a worksheet
Sometimes you need to jump to the last records of data quickly. To do this, use Control – End (for a Mac use Function – Control – Right Arrow). - Select a range of cells quickly
Control – A lets you select a range of cells fast. However, it depends where the cursor is. If the cursor is in a range of data, then Excel will select that range only. If the cursor is in a blank cell, then it selects the entire spreadsheet. - Hide the ribbon
The Ribbon at the top of Excel takes up a lot of space. To remove it, click control – F1 (for a Mac use Command – Option – R). - Move from sheet to sheet
Move to the next worksheet by pressing Control – Page down. Move to the previous sheet with Control – Page Up.
- Move to the edge of a range of data
If you have a large range of data, you can quickly move to the right, left, top, or bottom by pressing the Control – arrow key of the direction you want to go.
How to Cut Data Entry Time
Entering data into Excel is tedious and can require repeating the same process over and over. By cutting time on each entry, you can greatly improve the speed of the entire data entry project. Below are some data entering shortcuts to save time.
- Quickly copy your data down a sheet
Let’s say you entered a group of students’ names and grades. In the last column (Col E), you want to figure out the average. So you enter a formula to calculate the average.
Instead of copying and pasting this all the way down, you can double click the lower right of the cell with the formula, which is cell E2. This repeats the formula for each cell in the column.
- Get Excel to speak the numbers you type in
If you struggle to type the right numbers into each cell, ask Excel to speak the numbers after you type them to help prevent errors. Then you can hear exactly what you type without looking at the keyboard. (This is for Windows only.)
To do this, first you need to add the appropriate button to the toolbar. Go to File, Options, and then Customize Ribbon.
Once there, click the drop-down under Choose commands from and select Commands Not in the Ribbon
Scroll down to Speak Cells on Enter and click Add in the middle of the wizard to include this feature in the toolbar. (Add a New Tab to the ribbon first before you add commands to it.)
Once it is added, click on that menu on the ribbon; press the Speak Cells on Enter button.
If you want to stop the speaking, then press the button again.
- Freeze panes
When you have many columns and rows of data, it can be difficult to remember what each column is once you scroll down. This can lead to data entering errors.
Rather than lose column or row names while scrolling, you can Freeze the Panes. To do this, place the cursor below the column heading and to the right of the names. That would be cell B2.
Go to View and then Freeze Panes and then drop down to Freeze Panes.
This will make row and column headings stagnant so you can see them at all times.
How to Make Projections
When you have a data group, you may sometimes want to find a trend and predict future values. For example, you may have a small business and have many months of data for expenses. It would be helpful to find a trend to see what the future expenses would be in order to budget appropriately.
You can do this with a function called FORECAST. For example, consider this sample data of expenses:
There are three expenses with period-by-period tracking and 12 months of expenses.
Now let’s say you need to budget for future months to see upcoming expenses. One way to do this is to use the FORECAST function, which will look at the trend of the expenses and extrapolate into future numbers.
So you can go to cell N2 and enter in the following formula:
=FORECAST(N$1,$B2:$M2,$B$1:$M$1)
To break down this formula, you need three key components.
The first part is the current period they are in, which is cell N1. This is period 13. For this formula to work, you need to have the period numbers like they are in row 1. By putting a dollar sign in front of the 1, Excel will always refer to row 1.
The second part is the range of cells that Excel needs to find a trend for. In this case, the range is cells B2 to M2. You need to put a dollar sign in front of the B to anchor it, so when you copy it across, the first column is always B.
The third part of the formula is the range of the periods to look at: cells B1 to M1. Both the second and third parts need to span the same columns. For this, we need to put a dollar sign in front of the B to make it stay there and in front of both 1s (so it’s always looking at row 1). You just want to change the M as you copy the formula.
So once you do this, you can copy it down to row 4 and over to the right for period 14 and 15. This will produce a projection of future expenses.
You can see how Supplies and Equipment are trending up, while Food cost shrinks over time.
How to Use Tools for Analysis
Sometimes just looking at a large table of numbers isn’t enough to decipher what is going on. Excel has many tools to further analyze data.
One of the best tools to analyze data in Excel is the Pivot Table. A Pivot Table summarizes a large table of data into a smaller one to see the totals. First, start off with a table of data:
This is a table of purchases by employees and their categories and costs. A Pivot Table allows you to summarize it. To create a Pivot Table, place the cursor anywhere on the table where you’d like to summarize. Then click Insert and then Pivot Table.
The Pivot Table Wizard will then pop up. The range of the table should be filled in. If not, fill it in manually. Then you can choose to insert an Existing Worksheet or New Worksheet. Here, you’ll see a New Worksheet.
Next, you will see a shell of the Pivot Table without data in it:
The next step is to build it. To summarize all the purchases by employee, drag Person Buying over to ROWS. Then drag Cost over to VALUES. The result is a list of how much each person spent.
What if you want to know how many items they bought? You can drag Cost to VALUES again. But once you do, you need to change it from SUM to COUNT in order to count the number of items purchased.
After dragging Cost to VALUES, click on the right arrow and go to Value Field Settings.
The following wizard will pop up. Change SUM to COUNT.
You will now view a table of employees, costs, and total items purchased.
You can also summarize the data in the table by Purchase category. Pivot Tables are very flexible and allow Excel users to expand their analysis.
In Closing
Excel is a great tool to enter and analyze data, but without shortcuts, tips, and tricks, it can be extremely time consuming. Once you learn and execute these tricks, you can greatly improve efficiency and output.
For more office tips and tricks subscribe to our newsletter.