Pro Shortcuts That Make Excel Much Less Frustrating

Excel is one of those applications that’s fairly indispensable to most offices. It’s used for budgeting, email lists, and all other types of things like charts and data analysis. But it can also be frustrating and cumbersome to use if you don’t know some shortcuts.

Something such as separating a first name and last name that were entered into the same field on a list of 1,000+ records can take over an hour to do manually. But it you’ve got some Excel know how, it can take just a minute or two. 

Part of improving your business productivity and IT processes is making sure you and your team know how to navigate the software you use every day in the most efficient way.

Our BrainStomp team is going to help you do that with some time-saving pro shortcuts for Excel below. Enjoy!

Excel Tips That Will Make Your Life Much Easier

We’re going to start off with one of the biggest time-saving tips, which is automatically separating data from one cell into several, then move on to other tips that will make your life easier and your Excel use more productive.

Separate Values in One Cell into Several Cells 

Have you ever slapped your forehead in dismay when a colleague sends you a spreadsheet for a mail merge that has data grouped in one cell instead of several? “Do they even know how a mail merge works?!” you might silently mumble to yourself, because you know you’re in for a lot of work fixing that data. 

Unless… you know this tip.

Say that your spreadsheet has the first and last name in the same column, but you need them separated into different columns. Here’s what to do.

·      Make sure you have enough empty columns to the right of the one holding the data for each piece of data you’re separating out. 

·      Select the cells or whole column that contains the text values that you want to separate.

·      On the Data tab, in the Data Tools group, click “Text to Columns.” 

·      Follow the prompts in the Convert Text to Columns Wizard, it will ask you things like, if the values are separated by a space or comma.

·      The Wizard will automatically separate the data for you down the entire column or selection at once.

Put Data from a Row into a Column

Trying to figure out the best way to display data can take some trial and error. If you’ve typed several rows of data that you now think would be better displayed in columns, that can mean a lot of copy/pasting. 

·      You can do this with a lot less effort by using the Transpose function in Excel. 

·      Copy the row or range of cells you want to transpose.

·      Move your cursor and click into a blank column where you’d like to put the data.

·      Go to Home, Paste, and choose the Transpose option. 

Add an Extra Line Within a Cell

It can be frustrating when you’re trying type a lot of text into a field and instead of being able to hit “enter” to add a line, you’re forced to have all the text just run together.

You CAN add an additional line inside a cell in Excel, just do this: Hold down the ALT key while pressing Enter.

Let Excel Convert Your Decimals to Fractions

Would you like to give your team a report that displays data both in decimals and fractions? Doing all those conversions yourself can take a while, even if you’re using an online calculator. 

Let Excel do the conversion work for you.

·      Copy the cells with the decimal values you’d like to convert and paste them into a new column.

·      With the data highlighted that you want to convert, on the Home tab, Numbers group, click the drop down to get the pop out window.

·      Choose Fractions from the options and voila! Your conversion is done for you. 

Add Protections to Your Excel Workbook

If you’re sharing your Excel workbook with others, you may want to protect certain elements, such as:

·      Encrypting with a password

·      Making it read only

·      Protecting the workbook structure

·      Marking it as a “Final”

You can access all these options easily by opening your Excel workbook file and then going to File > Info > Protect Workbook drop down.

Use Format Painter to Easily Expand Row/Column Formatting

Using things like cell background colors can make reading rows and columns of data much easier and make your presentations look better as well. If you’re struggling with copying formatting over and over down a row, use this trick to copy as many times as you like. 

·      Highlight the cell/row/column that has the formatting you want to copy.

·      Double click the Format Painter icon on the Home tab.

·      Drag your cursor down the rows or across the columns that you want to paste the format to.

·      Click once more on the Format Painter icon to turn off the paste function.

Improve Your Everyday Processes with BrainStomp 

Optimizing the processes that your team does day in and day out can make a big difference in your company’s overall productivity. We can help you uncover tips, tricks, and optimizations to make your business better than ever.

Contact us today to schedule a technology consultation! Call 260-918-3548 or reach out online.