There is so much data to process these days, and one of the best productivity tools for that is Microsoft Excel. So in this month's installment of MITS Tips, I am sharing five Excel tips that will change your life, and I use them almost every day to make processing data a little bit easier. If you want to follow along using Excel, download my demo Excel Spreadsheet
and follow the steps as I do them in the video!
Tip 1 – Quickly Format Data Into a Table
The first trick that I want to show you is how to turn your bulk data into a nice-looking (and useful) table. This tip will make it much easier to work with your data and to drill down into it to see specific kinds of information.
anywhere inside your data
, and then find the icon labeled Format as Table
the color scheme
you want to use for your table. I'm going to use a light blue color scheme. Then you will need to determine whether you have headers on your table. Choose Yes
if you do, so that your table will format correctly.
Then, click OK
, and then your data has a nice color scheme. Luckily, there's more to it than just the color scheme!
Tip 2 - Quickly Size Columns
To begin, click
in the top left
corner to select all the cells in the worksheet, and then double-click
between any two columns to expand all of the data so that it fits a bit better.
Tip 3 - Quickly Filter Data
The advantage of putting your data in a table is that you can drill down into the data to see specific information that you need. For example, you can click on the drop-down filter arrows in the header row Payment Type
and select a type of payment to display (I chose Visa),
and you will then only see that payment type in your column.
You can do that same for the Country Column
, and now you will see just the Australia Visa payment information.
You can quickly clear any of these filters as well by clicking Clear
, in the filter drop down in the header row, and all your data is visible again.
Tip 4 - Quickly Move Columns and Rows
I always see people using the cut/paste feature to move columns or rows of data around—and you don't have to do it that way! It's quite simple to move columns and move rows of data around without having to use cut/paste.
First, select the Price Column
and hover your mouse over the edge of the column while also holding down Shift
on the keyboard then click and drag
the row to the new location.
And as you can see, I move the price column over to the end of the address section.
You can do this with rows as well using the same method.
Tip 5 - Quickly Split Data into Two Columns
I use this last tip quite often. Sometimes you will have a column with multiple types of data inside such as a first name and a last name, or maybe it has the state and the zip code, and you need to split that data into two separate columns.
First, take the last column, which for our example is Longitude. We want to split this because this is set to a whole number and a decimal place after. So, we are going to split it at the decimal point into two own columns.
Click at the top of your screen in the ribbon on Data
and then choose Text to Data
Under delimiters, select the Other
option, and since we are using the decimal point (or period) as our delimiter type a "." into the associated box.
Then choose Next
, and you will see how the data will break before confirming you want to do it. If it all looks good, as ours does, then click Finish. For our example, we now have the longitudinal minutes, and the fractional part of it as well in its own column.
Almost everyone uses Excel to manipulate different types of data, and some people are great at it, but I think we can all get a little bit better by using some simple tricks like these. Also, if you use Microsoft Outlook, these three tips
will save you a ton of time!
If you need more help using Excel or other Microsoft Office products, get in touch with us