Top 15 Microsoft Excel Hacks for Professionals in 2022

Reading Time: 14 minutes

Source

There are only a few people who could say they have mastered the art of using Microsoft Excel.  It is the most popular spreadsheet program used around the world for more than 35 years and still going strong. The Microsoft Excel supremacy is yet to be tested, definitely not by Google’s Sheets or LibreOffice. There is a good reason for that. Microsoft Excel has been an integral part of the IT industry for more than three decades and it is a brand in itself. It is able to perform all the functions you could ask for in a spreadsheet. We have been using the current Microsoft Excel version, as a part of Microsoft Office 2019 and belong to a Microsoft 365 subscription.

It is not only the numbers that are impressive for Microsoft Excel. Most users leverage Microsoft Excel’s infinite grids with data, using it as a flat-file database. It can efficiently act as a contact manager or a customer relationship management tool. There is no doubt that people have started using it as a word processor, despite the fact that Microsoft Word is included in the subscription model. Moreover, it can help you create loads of terrific charts with the data that you feed in.

There is one thing common between all Microsoft Excel users. Nobody can say that we know enough about the tool. There are a variety of ways in which you can use numbers, giving a new look to the data that it is not possible to count them all. However, it is manageable to learn some of the most promising tips that will help you in the long run. So buckle-up as we are going to go on an exciting ride of learning Microsoft Excel Hacks.

 

AutoFill Cells

Source

It is an often overlooked hack in Microsoft Excel. When you start typing a series of items like dates you know it is going to take a long time. So, start the series and move the cursor to the lower-right part of the cell. When it transforms in a (+) sign, click and drag down to select the cells you need to fill. They will automatically fill using the design you used. It can also go up a column, or left or right on a row. Moreover, you can AutoFill without any particular design too.

 

Link Breaks and Wrapping Text

Source

Typing in spreadsheets can be tiring, as the default for text you type is to continue without wrapping back down to a new line. It is possible to change that. You can create a new line by typing Alt+Enter as Enter alone will take you out of the cell. Click on the Wrap Text button under the Home tab at the top of the screen which means all text wraps right at the edge of your current cell.

In case of multiple cells that have text overruns, you can select them before you click Wrap Text. Select all the cells before you type in them and click Wrap Text. Then whatever you type will wrap going forward.

 

Paint Cells in a New Format

Source

Imagine if you change the entire look of a cell including the font, the color etc. If you want to apply it to other cells, you can use the Format Painter tool, it is one of the Home tab that looks like a paintbrush. Simply select the cell you like, click the icon, and then select a different cell to paint in the format. It will only match in look keeping your content as it is. If you wish to perform this on multiple tabs then double click the paintbrush icon and click away on multiple cells.

 

Flash Fill

Source

Flash will allow you to fill a column depending on the data design it witnesses in the first column. For instance, if the first column is all mobile numbers that are formatted like “1234567890” and you want them to look like “(123)-456-7890”, start typing. By the second cell, Microsoft Excel will recognize the design and show what it thinks you want. Simply hit enter and voila, it will be done. It works well with numbers, names, dates etc. 

 

Text to Columns

Source

Imagine you have a column full of names, first next to last, but you want two columns that break them out. Choose the data, then on the data tab click Text to Columns. You can separate them by using delimiters or by a fixed width. Fixed width is used when all the data is crammed into the first column, but separated using a fixed number of spaces or period. The rest is like magic, with extra options for certain numbers.

 

Paste Special to Transpose

Source

You have a number of rows. Now you want to convert them to columns or the other way. It will be a daunting task to move everything cell by cell. You can copy the data, select Paste Special, check the Transpose box, and click OK to paste into a different orientation. In this way, the columns become rows and the rows become columns.

 

Same Data in Multiple Cells

Source

You may need to write the same text over and over again in cells in a worksheet. That is a lot of work more often than not. Simply click the entire set of cells, either by dragging the cursor, or by holding the Ctrl key as you click each cell. Type it on the last cell, then hit Ctrl+Enter and what you typed is pasted in each selected cell. It is the same with formulas, and will change the cell references to work with any number of rows/columns the other cells are in.

 

Paste Special with Formulas

Source

Imagine you have a large amount of numbers in decimal format that you want to convert to percentages. Also, you do not want numeral 1 to be 100% but Microsoft Excel returns that when you click the Percent Style button. Now, if you want the 1 to be 1% so you have to divide it by 100. This is where Paste Special is handy. Start by typing 100 in a cell and copy it. Then, select all the numbers you want to reformat, select Paste Special and click the “Divide” radio button. Now, you will get the numbers into percentages. It also works when you want to add, subtract, or multiply numbers.

 

Use of Graphic in Charts

Source

You can include graphics in any element of a Microsoft Excel chart. Each bar or piece of chart can have its own image. For instance, let us take an example of PC Mag’s pie chart. You can choose to go with “no fill” too, which will give you a blank slice in the graph. Moreover, Clip art can be cut and pasted to an element for example dollar bills to show money etc. You must take help from your graphic designer to check them before you use them.

 

Hide a Whole Sheet

Source

Your Excel workbook can get filled with a number of worksheets. If you do not need any particular sheet, it is better to hide it rather than delete it. Hence, you can use the formulas from that particular sheet in other sheets. To do so, right-click the bottom sheet tab and select Hide. When you need to find it again, go to the View tab at top, click Unhide, and pick the sheet name from the list. There is another Hide button on the View tab menu at the top. This will hide the entire workbook but Microsoft Excel keeps running.

 

Conditional Formatting

Source

Looking at a large amount of data and wondering where the highlights are? Microsoft Excel’s Conditional Formatting will do everything from applying a border around the highlights to color code the entire table. It will also create a graph in each cell so it is easy to visualize the top and bottom of the range of numbers. There is another option for greater than/ less than so you can compare number changes. 

 

Data Validation

Source

Does your work involve making spreadsheets for others? It is quite easy to create a drop-down menu of selections to use in particular cells. Highlight the cell, go to the Data tab, and click Data Validation. Click on “Allow” and select “List”. In the Source field, type a list, with commas between options. It is possible to hide the data later and it will still work. This is a great way to restrict data entered in the sheet. For instance, you can select the date range and users cannot enter any dates outside this range. You can also customize the error message that the users will see.

 

Insert Screenshots

Source

Microsoft Excel makes it super easy to capture a screenshot of an open application on your desktop and insert it in a worksheet. Simply go to the Insert tab, select Screenshot, and you will see a menu showing a thumbnail of all the open programs. Choose one to insert the full-size image and resize it accordingly.

 

Insert Excel Data To Word

Source

At the time of its inception, the idea of merging data from Excel to Word or PowerPoint was utterly fascinating. Today, there is nothing new in it. As you are taking data cells or a chart, copy and paste in another program. If you change the data in one application, it will simultaneously change the data in all the other applications. You can use Microsoft Word’s Paste Special tool for that. Alternatively, you can go to the Home tab at the top, select the Copy menu, and use the Copy as Picture option.

 

Add without Formulas

Source

Do you have numbers in a spreadsheet you want to quickly calculate without the hassle of going to a new cell and making a SUM formula for the job? Microsoft Excel provides a quick and easy way to do that. You need to click the first cell, hold down the Ctrl key, and click a second cell. Take a look at the status bar at the bottom and you will see the sum of the cells calculated for you. You will need to keep pressing the Ctrl key and click as many cells as you like, the status bar will continue to show the sum for all the cells. Right click on the status bar next to get the Customize Status Bar menu and you can choose to include other elements that can be quick-calculated like the average or count of how many cells you clicked.