Imagine you’re a sales manager for a large retail chain in Europe. Suddenly, your finance colleague has come down sick with the flu in the middle of the monthly sales reporting. Your boss needs the hard numbers on her desk by tomorrow morning and your colleague has left a mess of data for you to sort out. Looks like you’re going to have to skip dinner plans with your friend tonight.
Or maybe not. Luckily, you remembered some Microsoft® Excel® tips that can help you get the work done in no time. With a few smart tips for organising even the most out of control spreadsheet, you can get this mess in order with less stress than you’d expect. Here’s how:
Tip 1: Text to columns
Let´s say you want to create a list of the company’s stores, with the location and the daily revenue. First, import a large dataset from your controlling software, which might look something like this:
Yikes, that’s a mess – but don’t panic. You do not have to go line by line and separate the dataset into separate columns. Those clever guys from Microsoft took care of it and enabled Excel with a tool that will help you clean up this data with a few clicks.
Step 1. Select the column with the data.
Step 2. Go to “Data” ribbon and click the “Text to columns”.
Step 3. Click “Delimited” and then “Next”.
Step 4. Choose the mark, which indicates the end of the columns (in our example we used a comma, so click “Comma”), then “Finish”.
What you get as a result is a tidy looking table, ready for further editing:
Tip 2: Paste special: “Transpose”
Now it’s time to make the spreadsheet more readable for your boss.
In previous articles, we have described the magic possibilities of the “Paste special” function, but that’s just the beginning. An additional tip to help you in this case would be the “Transpose” function, which saves a lot of time and nerves for anyone who has ever faced with a problem of converting columns into rows or vice versa.
Let’s say you’re still working with the same dataset. But now you want to have store locations as the column titles and daily revenue as rows. Here’s how:
Step 1. First, select the cells you want to convert (in this example, choose all three columns with the data) and copy it (Ctrl+C).
Step 2. Select the cell where you want to put your data and press the right mouse button.
Step 3. Choose the “Paste special” function in the list that showed up.
Step 4. Click “Transpose” and then “OK”.
This is what you get as a result: a nicely ordered spreadsheet that allows your boss to browse the stores from left to right and scroll down to see the revenue over time.
Tip 3: Custom sort
You’re almost there and the clock is ticking. But your boss needs the data in a very specific way. She asked you to organise the revenue with the three most established stores listed first. That means you need to list the main store first, then the two German stores and then all the rest listed alphabetically.
Unfortunately, you won’t be able to use the standard “sort” function, because the data you want to sort is in columns (not in rows) and your boss doesn’t want it in any standard order. Seems like the only way is to do it all manually… but not so fast! There are some additional possibilities in the sort function that can save you time – especially if you ever need to reuse this custom sorting across multiple tables.
Step 1. Select the data (without the row with the headers) you want to sort.
Step 2. Go to the “Data” ribbon and click “Sort” function.
Step 3. Click “Options” and then tick “Sort left to right”, then “OK”.
Step 4. Choose the row from which you want to sort the data.
Step 5. In the “Order” drop-down list, choose “Custom sort”.
Step 6. Type the new order you wish to use. Make sure that you separate each new value with a comma. In our example, it will look like this:
Step 7. Click “OK”.
You’ll get the following as a result. As you can see, the main store is listed first, followed by Munich and Frankfurt, and then all the rest.
Perfect timing, the spreadsheet is correct and you have just enough time to make dinner with your friend. Your boss gives you a thankful wave as you head out the door, but you know that all that hard work is due to a few easy tips from Excel.
*All these tips are based on Microsoft Office Excel 2010 (Windows) but other Excel versions offer the same or similar functionality. (Menus and performance may vary.) Microsoft and Excel are registered trademarks of Microsoft Corporation in the United States and other countries.
3 new tips for mastering Excel
www.stisitelkom.ac.id www.di.stisitelkom.ac.id www.ktm.stisitelkom.ac.id www.dkv.stisitelkom.ac.id www.dp.stisitelkom.ac.id www.srm.stisitelkom.ac.id www.blog.stisitelkom.ac.id www.multimedia.stisitelkom.ac.id www.elearning.stisitelkom.ac.id www.library.stisitelkom.ac.id www.repository.stisitelkom.ac.id www.cloudbox.stisitelkom.ac.id www.digilib.stisitelkom.ac.id www.mirror.stisitelkom.ac.id www.sisfo.stisitelkom.ac.id www.hilfan.blog.stisitelkom.ac.id www.telkomuniversity.ac.id www.stisitelkom.academia.edu www.kuningmas-autocare.co.id www.usnadibrata.co.id www.askaf.co.id www.hilfans.wordpress.com www.hilfan-s.blogspot.com www.profesorjaket.co.id