Excel at Excel with 13 awesome tips!

Microsoft Excel is an indispensable tool for all accounting and tax professionals. Even though you may be aware of the basics of Excel, having some quick shortcuts can be quite handy. It can save you considerable time and rocket your efficiency. We have gathered 13 tips that will prove helpful to you.

1. Auto-fill cells

The power of Excel goes beyond just shortcuts. Its agility is based on its hyper functionality to follow what you type, for instance in the column to the right of your data. It then gathers suggestions to fill the rest of your cells after you have typed some examples. This saves you from the onerous task of info-typing. You need to hit Enter to accept Excel’s suggestions.

2. Spreadsheet Shortcuts

There are plenty of formatting options when it comes to spreadsheets. It enables you to add special formatting without much effort. You can use the format painter function to mirror that formatting on other cells. You simply need to double-click on the format painter icon to lock the function and then single-click on any part of the spreadsheet that you want to format. Hit Escape to unlock the cursor.

There are some nifty shortcuts if you have to deal with large data. Give these a shot:

  • Format numbers to include two decimal places: Ctrl+Shift+1
  • Format as time: Ctrl+Shift+2
  • Format as date: Ctrl+Shift+3
  • Format as currency: Ctrl+Shift+4
  • Format as percentage: Ctrl+Shift+5
  • Format in scientific/exponential form: Ctrl+Shift+6

3. Highlight Data using Sparklines

As some of you may already know, Sparklines come as a built-in feature. With the help of Sparklines you can display small charts inside individual cells such as- line charts, bar charts, simple win/loss charts and so on. To create one, select the range of numbers you want to display, go to the Insert menu and select the chart option you want. Then, choose a location range along a single row/column in the same worksheet. With Sparklines, you can display trends in your data in a compact and neat manner.

4. Splice data easily

You can filter data with ease with this function. To do so, select any range in a table or PivotTable, and then go to Insert > “Slicer,” in the top right corner. Then, select the column you want to filter by.

5. Manipulate data with pivot tables

Pivot tables are a great tool when dealing with intricate and large data. Not only do they allow you to analyze data, but you can also detect patterns and make comparisons with these interactive tables. To make a pivot table, simply go to the Data menu and select PivotChart Wizard or PivotTable. The wizard helps you select the data in your PivotChart and format it in a convenient way.

6. Awe‘sum’!

A feature that brought respite to every Excel user. It gives you the total of a data set without the need for an equation. You need to simply click anywhere in the tables and press Ctrl+Shift+T. You will be furnished with the total of the total row that you created.

7. Check formulas and results with ease

When dealing with a large spreadsheet, you may want to check some formulas swiftly. To rapidly shift between the cell data and formula, press Ctrl+(~) keystroke.

8. Use the status bar without a formula

You can check the statistics of data in a table without typing any formula. All you need to do is select the cells you wish to see the stats on. Look at the bottom right of your window and you will find instant stats of the same.

9. Hide zero values

Often, users wish to hide zero values when dealing with huge data sets. Fortunately, Excel offers the option to hide zero values. Go to ‘File’ and choose ‘Options’. There, go to Advanced and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”)

10. Excel templates

All versions of MS Excel come with many templates. To create an amortization table with a template, right-click on a blank worksheet. Gain access to it with ‘Insert’. Under ‘Spreadsheet Solutions’, there are several templates, including amortization schedule. Excel also offers you the option to make your own template by saving a worksheet as an Excel template.

11. Instant Chart

To make an instant chart, just select the cells you want to include and press Alt+F1.

12. Find the worksheet you want

You can get a list of the most recently used workbooks by going to ‘File’ or ‘Office Button’, depending on the version you use. There, you will find ‘Recent’. This will save you time which would have otherwise spent looking for the worksheet you want.

Furthermore, in the ‘Advanced’ section, go to ‘Options’ and you can alter your view as well as adjust the number of recent documents shown. You may want to change the number to 50 for more convenience.

13. Create tables in a jiffy

First, click anywhere in your data. Then press Ctrl+T. This will produce an instant table displaying your data.

These tips and shortcuts can definitely help you become a wizard of Excel. Share them with your colleagues and friends and help them up skill too!

Connect with Us

Copyright © 2022 Excel Accountant. All Rights Reserved.