Excel Magic Tricks

Magical Excel Reports!

You may not be able to make a rabbit appear from inside your hat, but at the end of this article, you’ll have your coworkers thinking you’re an Excel magician! No this isn’t slide of hand or smoke and mirrors, these are useful tips and tricks that most Excel users have yet to see in action. Amaze and dazzle your colleagues with these quick tips.

Open Excel Files in Bulk—More of a windows trick rather than an Excel trick, but rather than opening files one at a time and having to manage each separately, CTRL+Click the various Excel files you want to open and then press Enter. Step back and watch as all of these files will open simultaneously!

Add a Diagonal Line to a Cell— Looking for more impressive formatting for your spreadsheets? Go to the regular place you’d go to add cell borders Home > Font > Borders>, then click More Borders. This menu brings up a wide variety of stylings and the ability to position a diagonal line within your cell.

Fast Navigation with Ctrl + Arrow Buttons— How many times do you see users scrolling for days trying to reach the bottom of the page. Show-off your magic skills by quickly jumping to the bottom line of data in the selected column by clicking CTRL + Down. Ready to return to the top? Spring back to the beginning with CTRL + Up.

Transpose Data from a Row to a Column— Sometimes you receive data only to realize it would be better off in Columns instead of Rows. Start by taking the data you want to transpose and copying it (CTRL+C), now find an open area for the new transposed data right click on the cell you want to paste in and select “Paste Special”. This menu includes a myriad of wonderful other tricks, but we’re going to choose Transpose and click OK. Presto! Your data is now transposed.

Transform the Case of Text— Whether you are working with First Names and Last Names or simply trying to clean up your data to be uniform we can easily change the case of your text. There are three common text types to consider: UPPER, LOWER, and PROPER. Upper changes the case of all letters in the cell to Uppercase, Lower to lowercase, and Proper will capitalize the first letter of every word in the cell. We’ll treat this just like any other formula. Select a blank cell and choose your case =PROPER then select the cell you would like change to make =PROPER(A1). Upon pressing enter you would have a cell that now includes the proper case matching A1. This can be especially handy for database management.

Rename a Sheet Using Double Click— Again pretty simple, but we’re suprised by how often we see people using Right-Click. On the worksheet tab simply double-click the name to rename directly in the tab.

What other Excel magic tricks can you share? Find additional helpful tips in our blog.

Want to see real magic? Check out what BizNet Software can do for your reporting and analytics. Dig deeper into the numbers, refresh your reports with updated figures rather than rebuilding reports, and automate your distribution with the click of a button. See how we can make Excel reporting fun!

Download BizInsight Overview Download BizBroadcast Overview