A 2018 study commissioned by Paxata found that about 68% of organizations depend on Microsoft Excel for data preparation, according to ComputerWorld. Microsoft Excel is an essential tool in most businesses because it allows them to manage large amounts of data efficiently.
Excel is also useful for scheduling, stock inventory, and accounting. You can even use it to prepare for tax season and collaborate with team members. However, using this spreadsheet program can be tricky for some individuals. So, if you struggle with spreadsheets, check out some of the best Excel hacks to help you boost productivity at work.
Understand Keyboard Shortcuts
Learning how to work with Excel keyboard shortcuts on Windows and Mac is an excellent way to speed up your work and eliminate the frustrations of working on spreadsheets. There are various shortcuts for each task, but you don’t need to memorize all of them. You only need to note down daily tasks that require you to work in Excel and then find out if there are existing keyboard shortcuts you can use. Some typical shortcuts used in Windows include Ctrl+N for creating a new workbook, Alt+= to add numbers in a contiguous range, and Ctrl+F to search, find and replace data in a spreadsheet.
Select All Cells With One Click
Whether you want to change font, text color, or make other changes on an entire spreadsheet, clicking one cell at a time can be tedious. To save yourself a considerable amount of time, click the light gray triangle on the top left corner where the top columns and rows meet. This simple step will highlight all the cells, even the empty ones.
You may also use the keyboard shortcut Ctrl and A to select all cells at once. Nonetheless, there are other simple tricks for selecting cells in Excel. For example, if you wish to choose a row of cells, all it takes is clicking the row number on the left-hand side.
Familiarize Yourself With Conditional Formatting
Most people know the basic formatting tricks in Excel, but have little to no knowledge of how to use conditional formatting. With this advanced formatting tool, you can improve the layout and design of Excel sheets to enhance spreadsheet readability.
There are several Excel tricks you can use to increase efficiency when working with conditional formatting. The most common are highlighting rows with duplicate values and multiple conditions, and selecting cells that contain text. For instance, when highlighting rows that contain duplicate content, start by choosing the range you want to format.
On the Ribbon, click Conditional Formatting, and select Highlight cell rules. Next, click on Duplicate values. Once you’ve done this, two dropdown options will appear. On the first one, click Duplicate and then select your preferred highlighting color on the second dropdown.
Delete Comments In Spreadsheets
Deleting a comment in a spreadsheet is straightforward. The first step is to right-click the cell containing the comment you want to delete. And then click Delete Comment on the pop-up menu. But when it comes to erasing multiple comments, removing one comment at a time can be daunting.
To speed up the process, use the Delete Command option. This trick entails selecting all cells using Ctrl+A and then clicking the Review tab in the Ribbon. Under the Comments group, choose Delete.
Alternatively, take advantage of Excel’s VBA feature to delete all comments on all worksheets. This trick can be intimidating for most basic-level users, but it’s manageable using a VBA code. Start by right-clicking any tab on the workbook you wish to delete all comments. Then click on ViewCode to open the VBA Editor window.
This will prompt the Immediate Window to appear. But if it doesn’t, click View in the menu and select Immediate Window. Then copy the code (For each ws in Worksheets: ws.cells.ClearComments: Next ws) and place the cursor at the end of the VBA code. Press Enter to delete all your comments.
Use Two Windows
In some instances, you need to go back and forth between two spreadsheets within the same workbook to compare numbers, create formulas, or format cells. Instead of flipping from one sheet to another, it would be much easier to view two worksheets side by side. You can create two windows of the Excel sheets you’re working on using a simple trick.
If you’re using a PC, go to View in the Ribbon, and then select New Window. If you’re on Mac, within Excel’s main menu, click Window and then choose New Window.
Data preparation and management are vital tasks in any business organization. You will be required to spend valuable time creating and organizing data in various formats, including charts, graphs, and tables. By learning basic Excel tricks like conditional formatting, keyboard shortcuts, and viewing data side by side, you can boost productivity. You’ll also gain the confidence needed to make informed data-driven business decisions.