5 Easy Tips for Excel

Ren_SmallSquare                                                           Picture of Earth to Symbolize Global Rating

 The following tips are fairly simple things you can do to make Excel less time consuming.  They are so simple you might wonder why no one has told you about them before!

Quick Sums and Averages

Suppose you are working on a spreadsheet in Excel, and you want to quickly check that the column of numbers you just entered matches the total you are expecting.  No need to click the Quick Sum button, just highlight the cells you want to total and look at the bottom of your screen.  Excel automatically creates an Average, Count and Sum calculation every time your highlight cells.

Excel Spreadsheet with Selection Showing Quick Sums, Averages and Counts

Shortcut Keys for Selecting

Excel has a lot of short cut keys that can really make your life easier if you have to do repetitive actions.  You can find a partial list at here.  For this post, I wanted to spotlight two ways of selecting large portions of a spreadsheet quickly.  When I’m working in Excel, I often want to select all or most of the spreadsheet.  A quick way to make this selection is to click on the top left cell in the block you want to select then use CTRL SHIFT  → ↓.

Spreadsheet with Selection Made with Ctrl Shift Arrows

However, using the CTRL SHIFT → ↓ is great only if you have a complete block with no missing rows or columns.  To make sure you get everything, use CTRL SHIFT END and the highlighting will extend all the way to the last used cell in the worksheet.

Picture of Excel Spreadsheet with Selection Made with Ctrl Shift End

Transpose a Table

Suppose you have a table in Excel and you want the columns to be rows and the rows to be columns.  There is a “quick and dirty” way to do it quickly.

1) Select the data you want to transpose. Copy.

Excel Data Selected for Transposing 

2)  Select a blank cell in your worksheet where you want the transposed table to appear; select the arrow for Paste from the Home tab.  The Transpose symbol is in the second row, third column of the Paste options.

Excel Spreadsheet Showing Paste Transpose Icon

Voila!  Your data has been transposed. The downside of the quick and dirty version is that the newly created table is not linked to the original table.  If you change something in the original table, it will not be updated in your transposed version.  There is a way to transpose while keeping that connection, but that will have to wait for a later post since it is a lot more complicated.

Display Formulas Instead of Values

Sometimes when you create a complex spreadsheet with a lot of calculations, it can be helpful to view all the calculations in the spreadsheet instead of the results of the calculations. This is particularly helpful when you are trying to track down an error.

Excel provides a shortcut to quickly do this:  CTRL `.

The ` grave accent key is located at the top left of your keyboard with the tilde.

Tilda Grave Key

Press once and the spreadsheet displays the formulas; press again and you get the results to appear again.

Excel Spreadsheet with Formulas Displayed

Getting the Most from the Quick Access Toolbar

If you use Excel a lot, you may find yourself using a specific command over and over again.  Paste Values is one I use a lot and I’ve frequently wished for a shortcut key to make my life easier.  An underused trick in Excel will allow you to create such a shortcut quickly and easily.

1) Click the down arrow at the right of the Quick Access Toolbar.

Quick Access -Toolbar

2) Click More Commands…

You can limit the number of commands you see by selecting “Commands Not in the Ribbon” from the “Choose commands from:” drop down box.

3) Select Paste Values (or your preferred command) and Click Add.

Quick Access -Add Paste Values

By default, Excel will add this to all your documents.  If you only want this shortcut to show up in the current document you can make that selection using the “Customize Quick Access Toolbar” drop down box (see the blue highlighted box above).

4) Click OK

Quick Access -Added To Toolbar

Now the fun begins.  Not only can you see this one click item all the time, but if you prefer to use keystrokes, you can access by holding down the Alt key and pressing the number corresponding to the position of the icon in the Quick Access Tool Bar.  In my case, I added Paste Value into the 7th position on the Toolbar so I can use Alt 7 to call on it at any time.

Quick Access - Hot Keys

Coming soon…

There are numerous tips and trick to Excel that can make your life easier.  I have only presented a few here, but there will be plenty more coming soon.  In the near future, we’ll take a look at the “Insert a Table” function and how this simple tool can be a truly powerful friend.  Later on, we’ll be looking at creating our own simple macros for personalized short cuts.  It’s easier than you think!

Welcome to Weeding the Numbers Garden!

Do you feel like your data is an overgrown garden?  Are you spending too much “quality time” with Excel?  If you want help harvesting insights quickly, you have come to the right place.

ren_leaflight-smallDr. Livingston, I Presume

Hi, Everyone!  I’m Ren Leaflight, the Business Intelligence Analyst for the College of Arts and Sciences at The Ohio State University.  Our college is quite large with 75+ majors and 100 minor degree programs.  All in all, we serve around 16,000 undergraduate and 2,500 graduate students.  Keeping track of all our students, faculty, departments and centers can be overwhelming.

It’s a (Big Data) Jungle Out ThereOrangutan in Jungle

Data everywhere is growing into a massive, tangled jungle – and OSU is no exception. When I was young, I loved imagining “Intrepid Explorer Ren” hacking through dense jungles and fighting off giant mosquitoes.  Nowadays, I prefer my data neatly organized – a data garden, if you will.  In this blog I will be covering some of the tips and tricks I use to tame the data jungle.

Weeding-the-Numbers-Garden Blog

This blog will explore tips and tricks to simplify your life with basic data exploration tools like MS Excel or MS Access.  Entries will range from general to advanced topics and each one will be clearly marked:

Picture of Earth to Symbolize Global Rating

Picture of Baby Using Computer to Symbolize Savvy Rating         Picture of Meditator in Front of Moon to Symbolize Guru Rating






We will also consider some of the newer data visualization tools on the market like Tableau and Datawatch.  MS SQL Server tips and tricks will be discussed in some of the “guru” positings.  We will discuss how to best answer your questions using our PeopleSoft query tools: eReports (Hyperion) and the soon to launch BuckIQ.  I will also explore some of the free tools available to download from the web that can help us tame with wildest data garden.

Guest Bloggers

Additionally, I will be inviting guest bloggers from around the university and from other higher education institutions to write about a topic where they are the experts.  They will help us discover new areas and gain a different perspective on the issues we all struggle to overcome.

Focus on You

Most of all, I would like to discover and explore the information and tools important to you.  I encourage you to chime in with your insight. Let me know what topics you would like me to cover. And if you have a topic you are burning to share, I invite you to be our next a guest blogger.

KittensRen's Three Kittens Lounging on Bed

And because data should be fun, here is a picture of my three kittens – Brigitte Lin, Jackie Chan, and Jet Li.