When it comes to working with important data, no software dominates the business landscape like Microsoft Excel. Depending on your level of Excel expertise, however, you may dread that complicated spreadsheet with its confusing columns, rows, formulas, and tabs. Unless you look at Excel for its possibilities instead of its pitfalls—any manual task can be automated, any data set can be dominated, and any goal can be achieved.
At CMIT Solutions, we know how important Excel is to your day-to-day operations. So we’ve collected five of our favorite tips and tricks to help make Excel work smarter, faster, and better for you.
(Note: some functions work slightly differently in different versions of Excel.)
Rather than manually clicking on and dragging over the cells you want to highlight, select the first one, hold down Ctrl + Shift, then press either the down or up arrow to highlight all the data in a column, or press the left or right arrow to select everything in a row.
As you work with an ever-expanding data set, you’ll likely need to add more rows and columns. But instead of performing this tedious task one by one, you can get it done fast. Need three new rows? Highlight three pre-existing ones, then right-click and press Insert.
Use this handy tool (most commonly represented by a paintbrush in the toolbar or found under the Format button on the toolbar) to quickly copy formatting from one part of a spreadsheet to another. Select an existing style you like—a shape, a cell, a picture border, a type of text—then apply it to cells that need to maintain regularity and consistency.
Need a series of ascending dates or other formatted numbers? Begin the series, move your cursor to the lower right portion of the final cell, and when you see a plus sign (+), click and drag to select all the cells you need to be filled. This can work up and down a column or left and right in a row.
Excel can combine various types of data from all sorts of sources. The trick is importing that data properly so you can create drop-down lists or pivot tables from it. Don’t copy and paste complex data sets; rather, use the options from the Get External Data selection under the Data tab, where you’ll see a menu that includes From Access, From Web, From Text, or From Other Sources.
Want to remove a row or column without actually deleting it? Easy—simply select the whole thing by clicking the letter or number header, right-click, and select Hide. Need to reverse the operation? Also easy—select the columns or rows surrounding the hidden one, right-click, and select Unhide.
Although this advanced function can seem complicated to the untrained eye, Pivot Tables can provide handy summaries of a large set of data. Save a new copy of your raw data set, then select Insert > Pivot Table. Excel can also try out Recommended Pivot Tables to draw out certain assumptions about your data, or even create a Pivot Graph to make information more visually appealing and easy to understand.
Although this once blew the minds of Office users, today it’s considered a standard part of the productivity suite’s limitless possibilities. Need a table, chart, or other spreadsheet-like graphical representation in your Word document? In Word, select Insert > Object, locate the desired Excel chart, and click Insert, then check the Link to file box so that any data in the original Excel spreadsheet that’s changed will automatically update in the Word document.
Diving into a spreadsheet created by someone else? Don’t fret—you can easily orient yourself and find out which formulas are used by clicking the Show Formulas button (or press Ctrl + ` on your keyboard) to display all of the formulas used in a workbook.
Need to deal with data that’s scattered across different tabs that requires repetitive calculations? With the worksheet containing the data you want to copy open, press Ctrl and click on the tab of the worksheet you want to copy it to. Click on the cells you need, press F2, and press Enter. This will re-enter the data into the same corresponding cell in the other selected worksheet.
Whether you’re a seasoned Excel pro looking for a few new usability tricks, or a rookie user scrambling to make sense of all those columns and rows, these tips should beef up your experience. Curious about a Microsoft Office upgrade? Looking for other ways to make your technological life more productive and efficient? Contact CMIT Solutions today. We’re here to make technology work for your business, not against it.