Excel Tips, Tricks and Shortcuts
Microsoft Excel Blog | Avantix Learning
You can delete blank rows in Microsoft Excel worksheets manually or you can use several tricks to delete multiple blank rows quickly. First, it’s essential that Excel users know how to select rows quickly using shortcuts or by using Excel commands such as Go to Special, Filter and Sort.
Moving a pivot table is not as simple as moving other objects in an Excel worksheet or workbook. You will typically need to use the Move PivotTable command in the Ribbon to move a pivot table to a different area on a worksheet or to a different sheet in the same workbook.
You can hide rows and columns in Excel using the context menu, using a keyboard shortcut or by using the Format command on the Home tab in the Ribbon. If you have hidden rows or columns, you can unhide them as well.
A pivot table can be deleted in an Excel workbook in several ways. You can delete a pivot table, convert a pivot table to values or clear data and customizations from a pivot table to reset it. When a pivot table is created from source data in a workbook,
Depending on your country or region, Excel may display decimal points or dots instead of commas for larger numbers. The decimal point (.) or comma (,) is used as the group separator in different regions in the world. You can change commas to decimal points or dots or vice versa in your Excel workbook temporarily or permanently.
You can group by month, year or other date period in a pivot table in Excel. One option is to group by date periods in a pivot table using the Grouping feature. Alternatively, you can also create calculations in source data to extract the month name and the year from a date field and use the fields in your pivot table.
If you want to break, update or change links to Excel charts or worksheets in Microsoft Word, you’ll need to use the Edit Links to Files command. Unfortunately, this command is difficult to find. To make it easier to access, you can add Edit Links to Files to the Quick Access Toolbar.
You can convert seconds to minutes and seconds in Excel by combining calculations and custom number formatting …
To remove blanks in pivot tables, you can set pivot table options to display data in empty cells, filter to remove blanks, apply conditional formatting, find and replace blanks or change pivot table design settings …
There are many different shortcuts for selecting elements in Microsoft Excel tables. You can use shortcuts to select an entire table, an entire row, an entire column or ranges in tables.
You can use keyboard shortcuts to increase or decrease decimal places. Press Alt to access the Ribbon …
You can easily show or hide formulas in a number of ways in Microsoft Excel. You can use a keyboard shortcut, click a button and even use a formula to show formulas …
You can use 3D references in Excel formulas to link multiple worksheets that are structured in the same way. A 3D reference refers to the same cell or range of cells on multiple worksheets.
As many Excel worksheets can become quite large, it can be useful to freeze row and column headings or freeze panes so titles are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.
It’s a common issue for Microsoft Excel users to attempt to use AutoFill to fill data in a filtered list and it doesn’t work as expected. The fill data is entered into all cells, including the hidden or invisible cells. The solution is to enter data into visible cells only …
You can insert subtotals in Microsoft Excel data sets or lists using the Subtotal feature. Check out these 10 great tips which include showing and hiding subtotals, formatting subtotals, copying only subtotals and grand totals and more …
It’s surprising how much time you can save with a few tricks and shortcuts for copying and pasting in Microsoft Excel. Here are 10 useful copy paste shortcuts for Excel users …
If you create a lot of charts in Excel, you’re probably looking for shortcuts to speed up creation and formatting. Here are 5 useful shortcuts you can use with your Excel charts.
Since pivot tables are one of the most powerful tools in Microsoft Excel for summarizing data, we’ve compiled a list of 10 more keyboard shortcuts for pivot table users.
It’s easy to lock and protect cells in Microsoft Excel to prevent users from changing data or formulas. This involves a two-step process …
Check out these 8 Excel tips and tricks for entering formulas. You’ll save a lot of time and even help reduce errors in your formulas.
Check out these 10 great Excel pivot table keyboard shortcuts. You can use standard shortcuts using the Control key or use Alt to access the Ribbon and create pivot tables in different ways.
You can easily delete blank rows in Microsoft Excel in a few steps rather than wasting time manually deleting blank rows one by one …
You can use a couple of Excel tricks to fill in missing values in a column. If you have blank cells in a list, many data tools such as sorting, filtering and pivot tables can’t be used efficiently …
You can save a lot of time selecting in Microsoft Excel using the Name Box. The Name Box is located to the left of the Formula Bar in Microsoft Excel. You can enter directly in the Name Box to quickly select cells or even objects.
If you want to break, update or change links to Excel charts or worksheets in PowerPoint, you’ll need to use the Edit Links to Files command. Unfortunately, this command is difficult to find. To make it easier to access, you can add Edit Links to Files to PowerPoint’s Quick Access Toolbar.
Check out this great list of Excel data entry tricks and shortcuts to save time in your worksheets.
You can turn on filtering for Microsoft Excel lists and tables and easily filter and sort data. Although most users will use the mouse to apply and remove filtering, you can also use your keyboard.
In newer versions of Excel, you can filter pivot tables by date periods using a timeline tool. You can combine timelines with slicers to filter in different ways and you can also zoom in on specific date periods.
Flash Fill is a great tool that you can use to quickly clean or format data in your Excel workbooks. It can automatically extract, combine and format data without using a formula. It does seem like magic. Here are 10 examples where we’ve used Flash Fill to clean up data.
Here are 5 different ways to quickly convert numbers stored as text to numbers in a Microsoft Excel worksheet. You can use a Smart Tag, Paste Special or …
In Microsoft Excel, when you enter data or a formula, it appears in the Formula Bar. If you are writing longer formulas, it can be helpful to expand the Formula Bar.
In Microsoft Excel, errors are flagged with small green triangle in the upper left corner of the cell. However, these indicators display when there may be an error but is, in fact, not an error.
Like PowerPoint and Word, you can access a free online version of Microsoft Excel and create simple workbooks online or open workbooks you or a colleague has created in the desktop version of Microsoft Excel in the online version.
Check out this trick to use Microsoft Excel’s awesome Quick Analysis tool to calculate totals automatically for a selected range of cells with the click of a button.
Keep an Eye on Important Changes using Excel's Watch Window Posted by: Avantix Learning Team Posted: March 24, 2016 Applies to: Microsoft® Excel® 2007, 2010, 2013 and 2016 The Watch Window is an awesome tool that you can use in Excel to view changes to cells that are...
You can easily copy or import linked Microsoft Excel worksheet data into Word documents. When you import, you can mport as unlinked data or you can link it. If it’s linked, you can update, change and remove the links.
You can highlight errors, blanks and duplicates in Microsoft Excel easily using conditional formatting. Conditional formatting is a great tool in Excel and if you combine it with formulas, you can do some amazing things.
Flash Fill is a fantastic tool that you can use to clean or extract data in Excel. Use Flash Fill to extract names, phone numbers, emails and even to format data.
Check out these great shortcuts you can use to zoom in and zoom out quickly in Excel using a mouse or a keyboard.
You can insert a live screenshot of an area in a workbook using Excel’s hidden Camera. The tool is a bit difficult to find and you’ll need to add the Camera to the Quick Access Toolbar (or the Ribbon) to use it.
You can lock and protect an Excel workbook by adding a password using your security options. If you want to prevent users from accessing or changing a workbook, password protection can be added to your Excel file in a number of ways.
Helpful Strategies for Printing Your Excel Data (and Saving Time and Paper) by Avantix Learning Team | Updated July 31, 2020 Applies to: Microsoft® Excel® 2013, 2016, 2019 and 365 (Windows) Most users at one time or another have printed an Excel worksheet and been...
In addition to adding comments in Excel, there is a relatively obscure function called the N function that you can use to enter notes directly within a formula. The N function converts a value to a number and has been around for a long while so you can use it in older versions of Excel as well.
You can insert, edit, delete, show and hide comments or notes in Microsoft Excel using Excel’s comments feature. Notes can also be entered directly in a formula. Notes or comments are hidden by default unless you click on a cell. You can quickly delete all comments using the Inspector.
Quickly Run Reports with Different Settings using Custom Views by Avantix Learning | Updated May 17, 2015 Applies to: Microsoft Excel 2010 and 2013 You can display several variations of the same Excel worksheet with different print settings, filters and hidden columns...
Microsoft Excel has many built-in shortcuts as well as the ability to navigate and select in the Ribbon using the keyboard. However, did you know that you can also write your own keyboard shortcuts for specific commands?
Check out these great Microsoft Excel keyboard shortcuts for formatting. You can apply currency, number, date and other formats using your keyboard.
Learn how to hide and unhide Excel worksheets. If you do choose to hide worksheets, you can still reference them in other worksheets or workbooks and unhide them.
Using a Keyboard to Navigate and Select in Task Panes by Avantix Learning Team | Updated September 10, 2017 Applies to: Microsoft Office 2007, 2010, 2013 and 2016 (Windows) This article is the third in a series of navigating and choosing commands in Microsoft Office...
Navigating and Selecting in Dialog Boxes Using a Keyboard by Avantix Learning Team | Updated September 10, 2017 Applies to: Microsoft Office 2007, 2010, 2013 and 2016 (Windows) This article is the second in a series of navigating and choosing commands in Microsoft...
Navigating the Microsoft Office Ribbon Using a Keyboardby Avantix Learning Team | Updated September 10, 2017Applies to: Microsoft Office 2007, 2010, 2013 and 2016 (Windows)This article is the first in a series of navigating and choosing commands in Microsoft Office...
It’s not difficult to hide formulas in Excel worksheets. Perhaps you don’t want a client to see the markup added to the final total or a commission calculation or you simply don’t want others to see your formulas. Simply select the cell(s) with the formulas you want to hide …
In Microsoft Excel, you have the ability to use AutoFill to automatically generate future values as a simple linear trend or an exponential growth trend.
In Microsoft Excel, you have the ability to add one or more trendlines to a chart and to project future results.
You can use sparklines to display trends visually in Microsoft Excel. Sparklines are miniature charts that you can display in cells, usually immediately to the right of the data you wish to summarize.
Go To Special is one of Excel’s most underused, yet useful features and, it’s not new. It’s been available in Excel for years. Go To Special enables you to quickly select cells of a specific type within your worksheet. You can find many useful things using the Go To Special dialog box including formulas, constants, blanks, visible cells and conditional formatting.
Save time in Microsoft Excel with these great selection shortcuts. You can use mouse and keyboard shortcuts to quickly select cells, ranges, columns and rows in Excel.
In Microsoft Excel, you can save lots of time if you can move around quickly in your worksheets and workbooks. You can use keyboard shortcuts to jump to the beginning or end of your worksheet and move up or down by ranges of data. In this article, we’ve put together a great list of navigation shortcuts to help you be more productive with Excel.
Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | email@example.com