Microsoft Excel can help you store and organize data for analysis and reporting so you can track progress, make sound business decisions, and more. And, while Excel is easy enough to start using right out of the box without much experience, here are some tips to help you make the most out of this spreadsheet software.
Read: Microsoft Office Productivity Tips
Microsoft Excel Tips And Tricks
Use Excel Keyboard Shortcuts
While there is a plethora of Microsoft Excel keyboard shortcuts that can help you save time, here are some of the essentials:
- Create a new workbook: Ctrl-N on PC, Command-N on Mac
- Select an entire column: Ctrl-Space on PC, Control-Space on Mac
- Select the rest of a column: Ctrl-Shift-Down/Up on PC, Command-Shift-Down/Up on Mac
- Select an entire row: Shift-Space on PC, Shift-Space on Mac
- Select the rest of a row: Ctrl-Shift-Right/Left on PC, Command-Shift-Right/Left on Mac
- Autosum cells: Alt-= on PC, Command-Shift-T on Mac
- Format cells: Ctrl-1 on PC, Command-1 on Mac
- Insert hyperlink: Ctrl-K on PC, Command-K on Mac
Add New Shortcuts
At the top of your Excel window, you can find shortcut icons for Save, Undo, Redo, and Home. Just to the right of those icons is a button that lets you add even more shortcuts for quick access, such as New, Print, Spelling, Sort, etc., and enjoy a more customized Excel experience.
Select All in Excel
You could select the entire spreadsheet using the typical select all shortcut of Ctrl-A, or you could get the job done even faster by clicking on the top left corner of the spreadsheet where the first row and column converge.
Use Quick Select To Avoid Mouse-Dragging
Suppose you need to select a massive group of cells, some of which are not on the screen. If you drag your mouse to select them, you could make a mistake, or it could take a long time. But if you use your mouse pointer and the Shift key, you can quickly select what you need with minimal effort and chance of error.
To avoid mouse-dragging when selecting multiple rows, columns, or cells, click on the first cell of the desired range with your mouse pointer. Next, hold down the Shift key and click on the last cell of the desired range to have them all highlighted at once – no mouse-dragging necessary.
Use The IF Function in Excel
IF is a popular Excel function that provides deeper insight into data by helping you compare values. IF statements have two results. The first result represents if your comparison is True, while the second result represents if your comparison is False.
Here is an example of an IF statement you can use in Excel for quickly comparing data:
=IF(F4>E4,"Profit","Loss")
Translated, the above IF statement means that, if the value in cell F4 is greater than the value in E4, the cell will display “Profit.” However, if the value in F4 is less than E4, the cell will display “Loss.” As you can imagine, using a simple IF statement can make it much easier to absorb data and give you a better understanding of what you are looking at.
How to Format Cells Faster in Excel
The Format Painter can help you save a ton of time when formatting different cells. Instead of going to each cell or group of cells and selecting the format, font, color, etc., to use, you can quickly copy and paste formats from existing cells to gain the uniformity you desire with less manual work.
To format cells with ease using the Format Painter in Excel, do the following:
- Click on the cell that has your desired formatting you wish to copy
- Go to the Home tab and look for the Format button with the paintbrush icon on it
- Click on the cell or cells you want to copy that format to
Add Multiple Rows And Columns in Excel
As you build a spreadsheet, you will probably need to add rows and columns as you input new data. Depending on how much data you have, you may need to add multiple rows and columns to accommodate everything. Instead of adding those rows and columns one by one, there is a shortcut that will help you add them even by the hundreds.
Suppose you have four columns or rows in your Excel spreadsheet, but you need to add four more. All you have to do is highlight the number of columns or rows you want to add (in this case, four), then right-click. Select Insert, and you will see the four new columns or rows instantly appear. Do the same whether you want 10 new columns or rows or 100. It does not matter.
Read: Microsoft Office 365 Review
Mark Something Finished With A Strikethrough
If you are collaborating with others or want to mark something as finished so you can take it off your to-do list, you can do so by adding a strikethrough. Simply select the cell or range of cells to which you want to add the strikethrough and press Ctrl-5 to have that horizontal line appear instantly.
How to Eliminate Duplicate Data in Excel
The bigger your spreadsheet, the more likely it is to have duplicate data that can make it harder to analyze what you have. Luckily, you can remove duplicates quite easily in Excel by doing the following:
- Highlight the row or column containing duplicates you want to remove
- Click on the Data tab
- Click on Remove Duplicates
- When the popup appears, select what data you want to work with
- Click Remove Duplicates
How to Filter Data in Excel
Pinpointing certain data for analysis in a massive Excel spreadsheet can be quite the daunting task if you do it manually. Use the filter data function, and it becomes a breeze, as it helps you pick and choose what data to see.
To filter data in Excel:
- Select the range, rows, columns, etc., that you want to filter
- Click on the Data tab
- Click on Filter
- In the first selected cell, you will see a small dropdown menu. Use it to select how you want to specifically filter the data
How to Create An Instant Border in Excel
If you want to make specific cells stand out with a border, select the cell or cells you want to border and press Ctrl-Shift-&.
How to Hide Rows And Columns in Excel
You can hide rows and columns with ease using some simple shortcuts. Hide a row by selecting a cell in it and pressing Ctrl-9. Hide a column by selecting any cell in it and pressing Ctrl-0. You can also use these shortcuts to hide multiple rows and columns. Select multiple cells in different rows and press Ctrl-9 to hide them, or select multiple cells in different columns and press Ctrl-0 to make them disappear.
Wrap Or Shrink Long, Unwieldy Text Or Links
If you are using Excel in the first place, it is probably because you want to give some sense of order to your data. Excessively long links or text can add disorder to your spreadsheet, which is where wrapping or shrinking links/text to fit comes in handy.
To wrap or shrink text in Excel, select the cells you want to format, then click on the Home tab. Click on Wrap Text if that is your desired option, or click on the small arrow next to it to select Shrink Text to Fit. It should be noted that any data in those cells will be set to fit your column width. If you adjust the column width later on, the text will adjust automatically to provide a perfect fit.
