Family Encyclopedia >> Work

14 tips to save time in Microsoft Excel

Microsoft Excel is a robust and complete tool for workbooks and spreadsheets. Whether you use it at work or home, using time-saving features means you can move on to your next task faster.

Here are several convenient ways to quickly fly through your workbooks, spreadsheets, and related tasks.

1. Templates to save time

One of the best time savers for most tasks is the use of a template. You can use them in Microsoft Word How to Manage Your Project with Word Templates How to Manage Your Project with Word Templates In project management, preparation is half the battle. To organize and document your project, you may need many different reports, forms, or plans. We've compiled a selection of Word templates to get you started. Read More In Excel, templates are just as useful. For project schedules, calendars, invoices, estimates, and more, taking advantage of these handy, pre-formatted tools can save you time.

Getting Templates How to Make Your Project a Success with Excel Templates How to Make Your Project a Success with Excel Templates Excel templates are an essential tool in the portfolio of an effective project manager. Even in the smallest project, templates can make your life easier and save a lot of time. We have compiled a selection. Read More Microsoft's website offers a variety of Excel templates for calendars, planners, expense trackers, and time sheets. Vertex42 has a good selection of schedules, budgets, to-do lists, and balance sheets. Another great resource is Worksheet 123 for invoices, inventory, sign-in sheets, and attendance forms.

14 tips to save time in Microsoft Excel

2. Freeze rows and columns

If you have a long spreadsheet that contains tons of data, you may have to scroll too far down or even to see it all. This means that you will lose sight of your headers and find yourself constantly scrolling back to view them. However, if you freeze the rows and columns, those headers will remain as you scroll through the spreadsheet.

  1. Navigate to View tab and select Freeze Panels on the tape.
  2. In the Freeze Panes dropdown menu, select Freeze Top Row , Freeze first column , or choose both if necessary.

14 tips to save time in Microsoft Excel

Now when you scroll up, down, right, or left, you'll notice that your headers will stay visible. To unfreeze those rows and columns How to Manage Columns in Excel How to Manage Columns in Excel Need to add, move, hide, or change columns in a complex spreadsheet? Do not panic. We'll show you basic Excel column operations to organize your data. Read More command again and click Defrost Panels .

Note that in earlier versions of Excel, the process is a bit different. Select the cell that is common to both the row and column you want to freeze, and then click Freeze Panes .

3. Operating the filling handle

The fill handler in Excel can save you a lot of time when you need to fill multiple cells and there are several different ways to use this feature. First, you can quickly create a list of numbers that count up by entering 1 in the first cell and 2 in the cell below it. Then select both cells and when the fill handle appears, simply drag to fill the cells as needed.

14 tips to save time in Microsoft Excel

By using the function for dates, you can easily fill a column or row in one-day increments. For example, you can enter 12/25/16 in the cell, select that cell, and when the fill handle appears, simply drag to add later dates. This maneuver also works with days of the week and months of the year, as well as down through a column and across a row.

14 tips to save time in Microsoft Excel
Another Practical Use of the Fill Handler How to Save Time in Excel Using the Fill Handler How to Save Time in Excel Using the Fill Handler Mastering the fill handler is the first step to becoming an Excel wizard. Read More If you're entering a word or letter, for example, select the cell and double-click the fill handle. This will enter that value into the remaining cells of the column as far down as the spreadsheet has data.

4. Transpose columns and rows

If you have a spreadsheet that uses column or row headings (or both) and you decide that they will work better the other way around, there is an easy process for making the switch. This saves you from having to rewrite those headers. Here are the steps to move cells from row headers to column headers.

  1. Select the cells in the column that contain the headers..
  2. Right click and select Copy or click the Copy button on the Home ribbon tab.
  3. Select the cell in the row and column where you want the headers to start.
  4. Right click and select Paste Special or click Paste and then Paste Special about the House ribbon tab.
  5. Select the checkbox near the bottom right that says Transpose .
  6. Click OK .

14 tips to save time in Microsoft Excel

5. Accessing the calculator

While you can use a plugin that makes a calculator appear in the sidebar of your spreadsheet, you can also take advantage of the built-in calculator feature. This is convenient when you want to do quick calculations that aren't done in your spreadsheet Doing your taxes? 5 Excel formulas you should know doing your taxes? 5 Excel Formulas You Need to Know It's two days before your taxes are due and you don't want to pay another late filing fee. This is the time to harness the power of Excel to put everything in order. Read more . And you can add the calculator to your toolbar or quick access toolbar.

To add the calculator to any location, start by selecting File> Options . Then choose any Customize Ribbon o Quick access to the toolbar , Depending on where you want it. In the Choose commands from dropdown box, collect All commands . Scroll down, click Calculator , and then hit the Add Button to insert it in the toolbar. Note that if you choose to add it to your ribbon, you'll need to create a custom group and add it there.

14 tips to save time in Microsoft Excel

6. Linking to tabs or cells

If your workbook contains multiple worksheets with data cross-referenced between them, creating a direct link provides quick access. This is especially useful if you're sharing a workbook so others can jump to that data quickly. Follow these simple steps to create the link:

  1. Select the cell that contains the data you want to link.
  2. Right click and select Hyperlink or go to Insert tab and click Hyperlink on the tape.
  3. In the pop-up window, select Place in this document .
  4. Then enter the text to display in that cell, a specific cell reference if desired, and the spreadsheet in the workbook with the data you are linking to.
  5. Click OK .

14 tips to save time in Microsoft Excel

7. Using keyboard shortcuts

If you are an avid Excel user, then keyboard shortcuts can save you time 3 Essential Excel Shortcuts That Will Save You Time That You Should Know 3 Essential Excel Shortcuts That Will Save You Time That You Should Know Excel can be a headache for the uninitiated, but with these shortcuts at hand, you'll save yourself a ton of time in the future! Read more . While there are almost too many keyboard shortcuts to list, here are a few that can speed up your tasks.

  • F4 (or Fn + F4 ) to repeat the last command, including formatting changes such as color or font.
  • Alt + H , my , ONE to clear the content, formatting, and other data attached to the selected cells.
  • Ctrl + W to close a spreadsheet and Ctrl + O to open one.
  • Ctrl + Shift + Colon to enter the current time and Ctrl + Shift + semicolon for the current date.
  • Shift + F9 to calculate the active worksheet.
  • Shift + Tab to move to the previous cell or option.
  • Ctrl + Home to move to the top of the spreadsheet.
  • Ctrl + Page Down to move to the next worksheet in a workbook and Ctrl + Page Up move to previous.

8. Working with AutoSum

Some may think that working with formulas in Excel is too time consuming to get into. But even for simple equations, these built-in functions can definitely move work out of the spreadsheet faster.

Unless you've moved or deleted it from your tape, the AutoSum the button must be in your Home tongue. This handy feature gives you the most common formulas with one click. You can add, count or average a group of numbers or get the minimum or maximum of them. Simply click the arrow on the AutoSum button to choose your formula.

14 tips to save time in Microsoft Excel

In addition to the AutoSum function, its Formulas The tab contains even more options. Each formula is grouped into a category to make it easy to find. You can select from financial, logical, mathematical, statistical, or engineering functions.

But for the most commonly used formulas, the AutoSum function is fast and convenient.

9. Using simple conditional formatting

Conditional formatting is another one of those Excel features that many may find intimidating. However, for the data you want to see appear on the page, it's a great tool.

For example, suppose you have a spreadsheet of survey data, and at a glance, you want to see how many Yes responses you have as opposed to No answers These steps show you how to apply a simple format.

  1. Select cells that contain Else answers.
  2. About the House tab, click the Conditional Formatting dropdown box.
  3. Choose Highlight Cells Rules and then Containing text .
  4. Type the word Yes in the box on the left and choose the format from the box on the right.
  5. Follow the same steps for the No answers.

14 tips to save time in Microsoft Excel

Then you will see all the Yes and No The responses have been formatted the way you chose, making them easy to spot.

If you plan to continue adding data, you can also apply this conditional formatting to the entire column or row instead of just a group of cells. This way, future data will be automatically formatted as you enter it. Do you want to learn more? Take a look at our dedicated article on conditional formatting. Automatic formatting of data in Excel spreadsheets with conditional formatting Automatic formatting of data in Excel spreadsheets with conditional formatting Excel's conditional formatting feature allows you to format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various daily tasks. Read more.

10. Quick graphics insertion

The charts feature in Excel is a great tool for displaying your data visually. And, you can choose from a variety of chart types, such as pie, bar, line, column, and many others.

Using the conditional formatting example above for Else Answers, you can insert a chart with a few clicks.

  1. Select cells that contain Else answers.
  2. About Insert tab, click Recommended Charts . With this option, Excel will take your data and place it in the chart type that best fits.
  3. If you like the chart, click OK and it will be inserted into your spreadsheet.

14 tips to save time in Microsoft Excel

This is the easiest and fastest way to create a chart in Excel and it only takes a minute. However, if you don't like the chart created for you, you can click the All Charts button. tab in the popup and experiment with other types.

To learn more about formatting your chart, learn all about Creating a Pie Chart How to Create a Pie Chart in Microsoft Excel How to Create a Pie Chart in Microsoft Excel Anyone can create a simple pie chart. But can you format it perfectly? We will take you through the process, step by step. Read more.

11. Sort with filters

When you have a spreadsheet that contains many columns of data, you may want to sort or filter all of them by a certain column. While there are several ways to do this, using a filter is the fastest and safest way to do it.

  1. Select the entire sheet by clicking the triangle button next to its first column at the top left.
  2. About the House tab, click Classification Filter .
  3. Choose Filter .

That's it! This quick move will put an arrow in the first row for each of your columns. When you click an arrow, you can sort the entire sheet by that column in any way you choose. If it's a date field, you can sort from oldest to newest, and if it's a text field, you can sort alphabetically.

Also, you can filter the data to see only the entries you want. When you click an arrow, you'll see check boxes next to its entries. Checking and unchecking these boxes will filter the data so you can see exactly what you need.

14 tips to save time in Microsoft Excel

The good thing about using the Filter The feature is that it does not negatively affect the rest of your data. If you sort an entire sheet, Excel will fit all the columns. And if you filter, Excel will just hide what you don't want to see.

12. Using format painter

If you use other Microsoft Office applications, such as Word, you may already be familiar with Format Painter. In Excel, this handy tool lets you apply the same formatting from one or more cells to another with a single click.

  1. Select the cell, cell group, row, or column to which you want to copy the formatting from .
  2. About the House tab, click Format Painter .
  3. Select the cell, cell group, row, or column to which you want to copy the to format .

This is a great way to quickly apply useful cell formatting to others without manual work.

13. Changing and seeing Windows

Are there times when you need to work with more than one Excel workbook at the same time? Maybe you need to review the data, compare it, or even copy it from one workbook to another. Excel provides a useful set of visualization features to help. Go to the View tab to see these options.

  • Fix all: When you click this button, you can organize all your open workbooks on one screen. Choose from a tiled, landscape, portrait, or waterfall view.
  • View side by side: This option allows you to choose two open workbooks to view side by side or on top of each other, which is perfect for comparisons.
  • Change Windows: Click this button when you have many Excel workbooks open at the same time and want to switch between them quickly. No need to minimize and maximize windows with this awesome feature.

14 tips to save time in Microsoft Excel

14. Installation of useful plugins

Office add-ins are similar to browser extensions in that they are tools to improve the experience, data, and efficiency of your application. And in Excel, you have plenty of useful add-ins to choose from depending on your needs.

Select the Insert tab and click Save . You can then check out tools by category. For example, the Productivity category contains plugins for document automation and a gallery of templates. And, the Project Management category has plugins for Gantt charts and activity timers.

14 tips to save time in Microsoft Excel

Some plugins are free, while others are paid, so be sure to check the details before installing one. And if you're looking for free Excel add-ins 8 Free Excel Add-ins to Create Visually Pleasing Spreadsheets 8 Free Excel Add-ins to Make Visually Pleasing Spreadsheets These Microsoft Excel add-ins can help you turn data into visually pleasing charts and graphs. Let your spreadsheets speak for themselves. Read More

What are your favorite timesaving features?

As we all continue to use Microsoft Excel, we keep discovering little tricks How to Fit Excel Spreadsheet to Your Screen How to Fit Excel Spreadsheet to Your Screen Tired of resizing Excel spreadsheets to fit your screen? that fit your screen? Here are three quick fixes that can make your spreadsheet a lot easier on the eyes. Read More

Do you have tricks of your own that you use regularly and would like to share with others? Please share those helpful tips in the comments below!