By: Deborah Mandel
I love Microsoft Office products, and one of my favorites is Microsoft Excel. For the general user, it is easy enough to figure out and allows you to view and sort data, create simple formulas, format cells, and more. For the advanced user, there are over 400 functions and formulas to help you do advanced calculations, programming and even more that is well beyond me.
For the most part, I consider myself a general user. I’m using Excel typically for work purposes and a little for personal use. Each time I use Excel I learn something new about its capabilities and I’ll tell you, I love it!
Here are a few tricks that I wanted to share:
(These tricks are specific to Microsoft Excel 2010. They may vary slightly on the newer version.)
- Fix your column width – You have entered all of your data, but some of it is longer that the column width. Instead of going column by column correcting the issue, highlight the whole table and double click on the line between the columns on the column heading. Instantly formatted.
- Sort or filter a column – Okay, now let’s say you have the data ready, but your boss tells you they want it in alphabetical order… easy! Highlight the column you want formatted and click on ‘Sort & Filter’ from the HOME tab. Select ‘Filter’. Now you will see a small arrow dropdown in that column. Click on it and select ‘Sort A to Z’. A sort warning will pop up and you will want to choose ‘Expand the selection’. This makes sure that the rest of your information stays with the correct row.
You can also use this feature to filter out specific information you are looking for. For instance if you want to find out how many in this list are from the USA, you will filter the country column, click on the dropdown arrow, and uncheck all of the countries except USA.
Here is what you end up with:
- Combine two columns – Let’s say you want to combine the ‘First Name’ and ‘Last Name’ fields. Insert a column and input =A1&” “&B1. This will combine the two columns. You can then copy and paste this into the rest of the column.
- Split a column into two cells – Now what about if you want to split one column into two? There’s an easy way to do that too. Let’s practice by splitting the names into two separate columns again. Insert a new column next to the one you want to split. Highlight the column that you are working with, then on the DATA tab, click ‘Text to Columns’. Select ‘Delimited’ in the popup window, as there is a character (the space) separating your data. Click ‘Next’ and select which character you want. Our data is separated by a space, but you can do this with almost any character; comma, semicolon, exclamation mark… the possibilities are endless.
Now click ‘Finish’ and check out your spreadsheet.
- Transpose date from a row to a column – Alright, this one is a fun one.
You may never have the chance to use it, but if ever a need arises, here you go! Let’s pretend that you have just finished your table and you realize it would look better if you had row headers instead of column headers. Rather than retyping all of your data (UGH!), just transpose it.
Highlight your table that you want to transpose, copy, and paste where you want it to go. When you go to paste, select ‘Transpose’ under the paste dropdown and VOILA!
My favorite way to learn new skills on Excel or any of Microsoft’s other products is to play around with a dummy document. Here is your homework: create a fake Excel spreadsheet (like the one I have been using) and try to go through each button on the HOME tab to learn what they do. I promise you will learn at least one new thing.
