You’ll often find it handy to be able to select all of the data in your spreadsheet, and clicking each cell one-by-one just won’t cut it when moving and formatting large quantities of data. Instead, either hit Ctrl+A or click the arrow button at the top corner of the spreadsheet to select all in seconds.
Despite Excel’s many capabilities, the simple task of typing in and formatting spreadsheet cells can be frustrating since text defaults to continuing in one line. Luckily that is easily fixed by holding down Alt+Enter to create a new line as you type. You can also click “Wrap text” in the top menu to have the text automatically wrap at the edge of the cell you’re in, and adjust as you resize the column or row.
There are so many different ways to accomplish tasks in Excel. Consider the simple copy and paste functions. As if Ctrl + C and Ctrl + V didn’t make copying easy enough, Microsoft built Paste Special. Here’s how it works. Say you want to copy and paste something but not everything like a just a number or format. Instead of copying everything and removing what you don’t want, you can use Paste Special. To do this, copy your information as usual, but instead of using Ctrl + V, right click and select Paste Special from the menu. Some of the more popular Paste Special options include:
Now that you know how to automatically update what prints in the margin, another time-saving trick is set up print areas for your tabs to update what prints within the margins. If you like to keep all your work on one spreadsheet but only need to print out a certain portion, setting the print area is a great way to save time when you hit Ctrl + P. To set your print area, highlight the cells you want printed. On the Page Layout ribbon, click the drop-down under Print Area and select Set Print Area. In the same drop-down menu, there’s the option to clear the print area. This is useful if you’ve changed your spreadsheet and want to add more to printable area.
While it seems everything is going paperless, sometimes there’s still a need to print. One of the best ways to keep track of what's printed out of Excel is by adding page numbers, timestamps and file paths to the header or footer. To make sure you don’t have to update these values every time you need to print a spreadsheet, you can add formulas that update automatically. First, change your view of Excel so you can see the header and footer. Next, add the following text to your header footer:
Speaking of a lot of data, Microsoft has amazing tools to handle it all, which probably explains why it became the most important workplace computer program around the world. Excel’s basic function for handling all this information is the Sort & Filter function. This is so useful because it allows you to rearrange and summarize data in a way that’s actually useful. To do this, highlight all the data you want included in the filter (a quick way to do this is Ctrl + A). Then, click the funnel icon in the top left of the Home ribbon. Now there will be a drop-down arrow at the top of each column in the filter. Here, you can select different values in the table and sort them. For example, let's say you wanted to see how many orders were made in a certain time frame. Just filter on the date column and select your desired time frame. From our example, you can quickly see there were only two orders in September 2014.
Looking around any version of Excel you’ll notice there’s an endless array of tools at your fingertips. But most beginners use just a few of these repetitively. Instead of switching between the different tabs of the ribbon every time, you can add your personal favorites to what’s known as the Quick Access Toolbar. Microsoft offers several ways to make this happen but the easiest way is to right-click on what you want to add and select “Add to Quick Access Toolbar”. One of my personal favorites in my QAT is the Sort & Filter function as I usually work with a lot of data. Before: After: Once you have your favorites added, you can also rearrange your QAT shortcuts by right-clicking on it and then clicking “Customize Quick Access Toolbar...” in the menu. With your customized QAT you’ll be blazing through your next spreadsheet with ease.
Some of the best Excel tricks come from the software’s series of simple keyboard shortcuts that allow you to maneuver around the document even more quickly. Here are some of our favorites:
Whether you’d like to hide a row or column, or inconveniently placed data, but keep it available for reference or formulas, Excel makes it easy. Simply select the cells, row, or column, right click, and select “Format cells.” Then go to the “Number” tab at the top, then “Category,” and select “Custom.” In the “Type:” field, type three semicolons, and click “OK” to hide but keep your data close at-hand.
If you need some guidance on what kind of info you’d like to apply to your data, Excel’s “Quick analysis” can provide you with some options, you guessed it, quickly. First select your data and click the quick analysis icon that appears to the bottom right of your selection. You’ll be greeted with a menu that can apply conditional formatting, tally up totals, or even call up charts in one simple click.