Microsoft Excel is an integral tool for most businesses. Some people relish the capabilities of Excel, finding it to be a useful tool that allows them to easily manage, report on, and illustrate tables of data.
Others, however, find it tedious and downright obtuse, unable to make heads or tails of what Excel can do for them aside from keeping things in neat columns and rows.
Are you the person who finds it confusing? That’s okay. We have some Excel tips that can make your life with this database multi-tool much easier (and more productive!). You might even have fun learning some Excel functions along the way.
10 Excel tips and tricks
Everyone loves tips and tricks. This is especially true when we’re talking about a tool as powerful and confusing as Excel. Below is a list of 10 easy Excel tricks, shortcuts, and hacks that will put you on the path to becoming an Excel super user.
There are many versions of Excel, including versions for desktop (Windows, Mac OS X) and mobile (iOS and Android) operating systems. The visuals here are screenshots from the Mac version of Excel. These tips were confirmed to work on a Mac and Windows desktop, but may apply to mobile versions as well. This piece also discusses keyboard shortcuts for use in Windows OS. For Mac, replace the “Control” key with the “Command” key.
1. Select all cells in a spreadsheet at once
Do you need to reformat your font or make some other sweeping change to your Excel workbook? An easy way to select all the cells in the document is to click on the square in the upper left-hand corner where the top of the rows and columns meet.
Clicking on this box will highlight all of the cells in the document, even empty cells.
If you prefer keyboard shortcuts, you can also select all cells if you press the CTRL and “A” buttons (CTRL + A) at the same time.
This is also a great way to format cells all at once. Do you have a formula wrong or want to change a whole bunch of cell rules? You can save a lot of time by making all these changes at once using the select all function.
2. Copy a worksheet from one workbook to another
Are you ever in an Excel file and suddenly realize you need to duplicate one or more large data sets? If you have a ton of formatting, manually entering this data is a nightmare. This is where copying a worksheet from one workbook (a collection of multiple sheets) to another comes in. Follow these steps to do it:
- Start by opening your “source” workbook (the one with the data you want to copy).
- Next open your “target” workbook (the one you want to copy to). This can be a new workbook or an existing workbook.
- In your source workbook, look towards the bottom left-hand corner and find the name of the sheet you want to copy. Unless you’ve changed the name of the worksheet, it should have a name like “Sheet1” or something similar.
- Right-click on the sheet you want to copy (if you’re on a Mac with a single-button mouse, you may need to hold down the Command key while clicking).
- Select “Move or Copy …” from the menu.
- From the list, select where you want to move or copy the sheet to. You’ll need to click on the dropdown at the top to see other open workbooks.
- Choose the workbook to copy it to, and select where in the order of the existing worksheets you want it to be.
Alternatively, you can move the worksheet from one workbook to another by dragging it with your mouse, but it might be safer to copy it, at least until your comfort level with Excel increases.
If you ever have extensive data validation to do and want to avoid butchering an entire workbook with a botched formula, this copying method can be a quick and easy way to make a backup workbook.
3. Add multiple rows or columns at once
You might find you need to add multiple rows and columns at one time. For example, let’s say you want to add multiple rows in the middle of a set of data. You’ll need to:
- Select two rows, starting with the row directly below where you want the new rows to appear. You can choose as many rows as needed.
- Right-click (PC) or Command-click (Mac), and select “Insert” from the dropdown menu.
- Your new rows will appear above the first row you selected.
Because you’ve highlighted rows, Excel understands that you want to insert rows and will insert the number highlighted. In this example, two rows have been highlighted, so two blank rows will appear when “Insert” is selected. But if you select columns, it will work the same way.
4. Filtering data
By clicking on the “Data” tab at the top of the page then clicking “Filter,” you will give each column it’s own clickable dropdown menu on each cell in the first row. Click one, and you can sort data in a variety of ways.
From the list that appears, you can choose certain values or names. Simply unclick “Select All,” then click on the names you want. Once you hit “OK,” the dropdown menu will disappear and show you just the names you had selected.
The list has now been truncated to include the values you chose. But as you can see by the circled row numbers, the other data hasn’t been deleted. It is simply “hidden” in this view.
You can easily undo any sorting by clicking on the “Filter” button at the top and choosing “Select All” again.
5. Copying figures from one worksheet to another in the same workbook
You can use a figure from one cell on one worksheet in another cell on another worksheet without having to copy and paste them. This is especially handy for calculations where a number might change month to month, but you have one worksheet that figures everything on a year-to-date basis. To do it:
- The example below shows the first tab of the workbook, which displays year-to-date sales, expenses, and revenue totals.
- The second tab is labeled JAN, and the third tab is labeled FEB.
- To determine the sum, you need to enter the formula below in the desired cell =SUM(JAN!B3+FEB!B3). The formula is “WorksheetName!WorksheetCell” without quotations.
6. Copying a formula across rows or down columns
You can copy any formula across a range of cells using your mouse. With the previous formula as an example, it only needs to be input once, in the B2 cell. Once it’s entered, click on the cell, then click on the little box that appears in the lower right-hand corner. Drag it across the desired range of cells, and you will populate the range using the same formula.
This copies the formula across the cells, but changes the values relative to the new cell’s position. For example, in the B3 cell, the formula will read “=SUM(JAN!B3+FEB!B3)” without quotes. In the C3 cell, the formula will read “=SUM(JAN!C3+FEB!C3)” and so on.
The same thing can be done if you drag the box downward instead of across. This copies the formula into the column. So the B3 column formula will read “=SUM(JAN!B3+FEB!B3)” and the B4 formula will read “=SUM(JAN!B4+FEB!B4)” and so on.
7. Transpose columns and rows
If you need to move pre-existing data from columns to rows — or vice versa — don’t despair.
- Select the data you want to transpose, and copy it (CTRL-C).
- Click on the cell where you want to place the data, then right-click to get the dropdown menu. From that menu, select “Paste Special…”.
- From the Paste Special menu, click the “Transpose” checkbox in the lower right-hand corner, then click OK.
- The data is will flip with rows as columns and columns as rows.
8. Continuing a series down a column or across a row
If you’re inputting a numbered list but don’t want to number every row, there’s a quick way to do it in Excel.
- Start your series of numbers.
- Next, click on the first cell in the list, and drag down on the small box in the lower right-hand corner (similar to what we did in tip six). The cells will auto-populate with what Excel believes you’re trying to do. (It can be intuitive and figure out that it’s a numbered list, a set of dates, etc.) But in the case below, you can see that it simply copied a 1 into each cell in the column, and that’s not what we want. So we’re going to need to make some changes.
This will adjust the values in the cells so that they become a numbered series. As shown below, the program will make this adjustment for however many cells you have highlighted. So if you want it numbered from 1 to 100, highlight 100 cells.
9. Highlight a list of numbers to see basic information
This is a favorite of many Excel super users because it’s simple and so helpful, especially when dealing with figures and finances.
If you highlight a group of figures, Excel will display a few “facts” about them in the lower right-hand corner, namely, the number of cells that are highlighted, the total sum of the cells, and the average of the cells’ values.
This is really handy if you’re looking for a quick total or average without having to enter a formula.
10. Essential shortcut keys and tips
Here are some other shortcuts that you may not be aware of. Some of these are universal across all Microsoft Office products (e.g. Word, PowerPoint, etc.) but are still very helpful when using Excel.
- CTRL Z (or undo button): This command undoes the last action you took in the worksheet. You can use the CTRL-Z keyboard shortcut, or the backward arrow in the upper left hand corner of the Excel window, above the main navigation menu.
- CTRL-SHIFT-Up or Down Arrow: Selects all of the cells directly above (up arrow) or below (down arrow) the selected cell. This only selects cells above and below that have data, so it won’t select empty cells.
- SHIFT-F11: Creates a new worksheet in your current workbook called “Macro1.” This shortcut is also referred to as the “insert tab” shortcut. In Mac OS, you may need to add the “Fn” key for this shortcut to work.
- CTRL-Home: Navigates to cell A1.
- CTRL-End: Navigates to the last cell that contains data.
- Format Painter: Select the cell whose format you want to copy. Then click on Format Painter (the little paintbrush) in the upper toolbar or click on the paintbrush in the pop-up format menu that appears and click on the cell you want to format. In the example below, we clicked on the “Expenses” cell, clicked on Format Painter, then clicked on “Revenue.”
Making a new best friend in Excel
When starting a new business, you probably thought of the real dollar signs you’d work with, not the ones in your data tabs on Excel. But with the above Excel tips and some perseverance, you’ll be quickly filling those blank cells with important data that allows you to do a quick analysis of your business’s progress. (As an added bonus, you’ll have a new sense of superiority around those new Excel users.)
Your journey toward Excel skills mastery starts with that first column and will go on for many years as you learn more and more advanced Excel functionalities. Now go forth, small business owner, and fill every single cell of your worksheets with reminders that your business exists, and you are a rockstar!
Hungry for more tips? Our accounting checklist would pair nicely with your Excel sheets and will help you get your accounting started on the right foot.