10 Excel Tips to Help You Become a Pro at Spreadsheets

 

There are very few individuals on the earth who can claim to know everything there is to know about Microsoft Excel. It's the world's most popular spreadsheet programmer, and it's been the industry standard for more than 35 years, having supplanted the venerable Lotus 1-2-3, the first breakthrough software for PCs in the 1980s.

Microsoft Excel's spreadsheet supremacy has yet to be fully tested, not by Corel's Quattro Pro (still marketed today in WordPerfect Office), LibreOffice's open-source tools, or even Google Sheets (the spreadsheet part of Google Drive).

That's because there's a reason for it. Excel is more than just a well-known brand; it is also a formidable tool. It performs almost everything a spreadsheet could possibly accomplish. The latest Excel version, which is included in Microsoft Office 2019 and may be obtained through other means such as a Microsoft 365 subscription, is a popular choice (many business listings).

It's not merely for the sake of numbers. Many individuals use Excel as a flat-file database, filling its apparently unlimited grids with data. It may function as a contact manager or a full-fledged client relationship management. Despite the fact that Microsoft Word is usually right next to it, it's not surprising to see individuals using it as their word processor. That's not even taking into account the nearly endless amount of great-looking charts it can make with the correct (or even incorrect!) data.

Almost every Excel user has one thing in common: a lack of knowledge. It's hard to list all of the different methods to slice and dice numbers, to give data a new appearance, and so on. On the subject, entire volumes have been written. However, some of the most fascinating and sophisticated strategies that will make your time with the application a bit simpler and make you appear like a high-tech spreadsheet genius are simple to grasp. So brush up on any of these entire Excel tips to help you succeed.

1 - Transpose with Paste Special

You have a lot of rows. They're supposed to be columns, right? Alternatively, vice versa. Moving things cell by cell would drive you insane. To paste data into a new orientation, copy it, choose Paste Special, check the Transpose box, and click OK. Rows become columns, and columns become rows.

2 - Hide in Plain Sight

To conceal a row or column, just select it all by clicking the letter or number heading, right-clicking, and selecting "Hide." (You may unhide the columns on either side by selecting them all at once, right-clicking, and selecting "Unhide.") But what if you only want to hide a small area of inconveniently situated data while still being able to work with it? Easy Right-click the cells and select Format Cells from the menu. Select "Custom" from the Category drop-down menu on the Number tab at the top. In the Type: field; type three semicolons (;;;). Click the OK button. Although the numbers are no longer visible, they can still be used in formulas (business listings).

3 - Select with Ctrl+Shift

There are much more efficient techniques to choose a dataset than moving the cursor with the mouse, especially in a spreadsheet with hundreds of thousands of rows or columns. Hold down Ctrl+Shift while clicking on the first cell you wish to pick, then use the down arrow to obtain all the data in the column below, the up arrow to get all the data above, or the left or right arrow to get everything in the row (to the left or right, of course). When you combine the directions, you'll receive an entire column as well as everything in the left and right rows. Only cells with data will be selected (even invisible data).

The cursor will go to the lowest right-hand cell with data if you press Ctrl+Shift+End, choosing everything in between, including blank cells. That's all there is to it if the pointer is in the upper-left cell (A1).

Ctrl+Shift+* (the asterisk) may be quicker since it selects the entire contiguous data set of a cell, but it will halt at blank cells.

4 - Inserting a screenshot

Taking a snapshot of any other open programmer on your desktop and inserting it into a spreadsheet is quite simple with Excel. Simply pick Screenshot from the Insert tab, and a drop-down menu will appear, providing a thumbnail of all active apps. To embed the full-sized image, choose one. You may resize it to your liking.

5 - Make a quick analysis

If you're not sure what information you'd like to apply to data in Excel, use the Quick Analysis menu to swiftly cycle through alternatives. Select the data and then select the Quick Analysis box in the lower right corner. A menu will appear, with choices to apply conditional formatting quickly, generate charts, and manage totals, show spark lines, and more (free business listings).

6 - Same Data in Multiple Cells

For whatever reason, you may find yourself writing the same item over and over in a worksheet's cells. That is agonizing. Simply click the full set of cells, either by dragging your mouse over them or by pressing and holding the Ctrl key as you do so. Type it in the last cell, then press Ctrl+Enter (not Enter alone) to have it appear in each of the chosen cells.

This also works with formulae, changing the cell references to match the row/column in which the other cells are located.

7 - Excel Shortcut Keys That Will Make Your Life Easier

Excel, like any good programmer, contains a plethora of useful keyboard shortcuts. Here are a few of our favorites. Ctrl+; —Inserts the current date. Ctrl+Shift+: This key combination inserts the current time (the colon is what is in a clock reading, like 12:00). Ctrl+Shift+#—Modifies the date format. Strikes through the text in a cell with Ctrl+5. Deletes the current column by pressing Ctrl+0. Hides the current row by pressing Ctrl+9. Switches between open worksheets with Ctrl+F6 (that is, open Excel files in different windows). Ctrl+'—Up by the 1 key, that's the accent mark. This combination changes the sheet's display to show all formulae. Quickly switch between sheets in the currently active worksheet by pressing Ctrl+PageUp or PageDown. Start modifying the currently selected cell by pressing F2 (much faster than double-clicking). Shift+F10—Displays the right-click menu for the currently selected cell.

8 - Formulas with Paste Special

Let's imagine you have a large number of decimal values that you wish to display as percentages. The issue is that that numeral 1 shouldn't be 100%, but that's what Excel provides you if you merely click the Percent Style button or hit Ctrl-Shift-%

You want that 1 percent to be accurate. As a result, you must divide it by 100. Paste Special comes in handy in this situation.

To begin, type 100 into a cell and then duplicate it. Then pick all of the numbers you want to reformat, go to Paste Special, click the "Divide" option button, and voila: you've got percentages converted to numbers. Obviously, this may be used to quickly add, subtract, or multiply integers.

9 - Conditional Format

Are you looking through a large quantity of data and trying to figure out where the highlights are? What are the top five, who has the highest (or lowest) score, and so on? Conditional Formatting in Excel can do everything from add a border to the highlights to colour-code the whole table. It'll even insert a graph into each cell so you can see the range's top and bottom figures at a glance. (The greatest numbers are in fast green, the lowest in stopping red, and there's a spectrum in between.) Create extra rules in the Highlighted Cells Rules sub-menu to check for things like text that contains a certain string of words, repeating dates, duplicate values, and so on. There's also a larger than/less than option for comparing changes in numbers.

10 - Charts may be saved as templates

Although Excel offers a wider range of charts, it's nearly hard to locate a default chart that's ideal for your presentation. Excel's capacity to personalize all graphs, thankfully, is exceptional. It's a bother, though, to have to rebuild one. It doesn't have to be like that. Make a template of your original chart.

Right-click on a chart once it's perfected. Choose the option to save as Template. In your default Microsoft Excel Templates folder, save a file with the CRTX extension. After that, implementing the template is a piece of cake. Select the data you wish to chart, and then go to the Insert tab, Recommended Charts, All Charts tab, and Templates folder. Select the one you want to use in the My Templates box, and then click OK.

Some components, such as the actual text in legends and titles, will not translate unless they are included in the data set. You'll have access to all font and colour options, as well as embedded graphics and series settings.