Excel for Telecom & IT - Part 2

Monday, February 21, 2011 5:32:00 PM Categories: Excel
Rate this Content 1 Votes

Today we'll continue with Excel Tips, aimed at the area of Telecom and IT.

Some quick tips, and sometimes unknown to some users. Always learning new tips you can not only perform everyday tasks faster and simpler, but also become an increasingly differentiated professional.

So let's go to the brief tutorial today.

 

Move Column

Ever needed to move a column in an Excel table?

Assume the following: that you have a table in Excel, with some columns of data. Suppose further that you need to do a reorganization on that table, only to be more 'natural'. For example, it may perhaps your table has an 'address' field early on, then multiple data fields, and then a field for 'country'. Ideally address is around the country, isn't it?

Anyway, this example was just to illustrate the need, sometimes we need to reorder the columns, whatever the reason.

The most natural way, and almost instinctively do (it works!) is as follows: First, locate where is the column that should be moved (rearranged). Then select the entire column, and choose Menu Cut (or CTRL + X).

 

Then go to the new location of this cut column, select the column before or after, and then paste it (CTRL + V).

 

Well, there's nothing wrong with that, and the result works perfectly!

 

But is there an even easier and more intuitive way to do this? Because, even in the above example, some people have a certain 'fear' of losing your data away by the way, and first create a blank column, do CTRL + C and CTRL + V and then verify that the data was copied off the column blank.

Yes, there is an even more elegant way to move columns: using SHIFT!

Reworking the example, to move the column right now with the SHIFT key and mouse, first select the column you want to move. Then, press the SHIFT key and keep it down (do not release it). With the mouse, click on one of the highlighted lines of the selected column, and move - drag with the mouse - to the new location. When the column is that new location, release the mouse, and finally release the SHIFT key.

 

The result is the same as shown earlier. However, this new way of working with the data - moving instead of just copy / cut and paste - eventually results in greater productivity - you'll notice over time.

 

Move Rows

Everything we say for columns, also applies to rows. The reasoning is exactly the same (move the row with the mouse and SHIFT). Consequently, waiver examples.

 

VLOOKUP

No doubt, the most famous and effective Excel functions is VLOOKUP.

Okay, you may have never used this function till today. And maybe, wants continue not using it. But it is also true: you're missing one of the most powerful and useful functions, which can help you a lot!

The main reasons that make us sometimes do not take advantage of tools or functions like this is just ignorance - that is, do not know they exist, do not know what to do with them, and especially do not know how to use it.

To remove this barrier at least in relation to VLOOKUP, let the explanation come.

 

As always, by example, suppose you have a table with some fields as shown below.

 

Imagine someone asks you: "For the BTS type C, which is the Serial?".

 

A natural way is to open the table in Excel. Check the field-line "BTS Type" until you find the type 'C'. When you find it, check the serial on the same row - in column 2 of that table.

 

Sure, but how to do this AUTOMATICALLY? It's simple, and of course you already linked: Using the VLOOKUP function, which is a search function!

Remember: to insert a function in Excel, just start typing into any cell the character '= '.

 

In our case, typing "= V" shows a list of functions with the letter V. Double click with the mouse in VLOOKUP function, and are ready to configure our function.

 

First, notice that the function has several arguments, ie, the inputs necessary for it to return something. Let's try to simplify the descriptions:

  • lookup_value: what we are seeking?
  • table_array: where we do look for?
  • col_index_num: in which column - in the table where we are looking for - is the data to be returned?
  • range_lookup: the seek must be exact or approximate?

This last argument can be left blank, but I advise always put 0: Excel will do an 'exact' search. Note: One observation here is that if you want to look closely (putting 1 or blank) remember to order the first column in ascending order, to ensure that the function returns the correct value.

 

Let us conclude. First input the value that we'll seek: BTS Type C. For convenience, the value C is already typed in a cell (in my spreadsheet was E16), we can make a reference to it. So we started setting up the function:

 

After entering the value you wish to look for - that is in cell E16 in my spreadsheet as said, type a comma*, and Excel ask me to tell where you want to search. (* sometimes it can be semicolon, depending on your Regional Settings).

With your mouse, simply select the location of data. You can also select entire columns when appropriate.

 

There, I told him what I seek, and also where I want to search. Now, just need to tell which column of my table where I'm looking for is my desired data.

In our case, is right in column 2, ie, the first column, immediately after the first column, which is used as reference in the search. If you're confused, read it again, but trust me, it's not complicated, and soon will become clearer.

 

Right. We still have the last argument: exact search (0) or approximate (1 or a blank). As I want it do an exact search, I type a zero.

 

Now, just press Enter! See the result.

 

Absolute Range

Just to finish, one more small tip when you are selecting the data range for the search. So, whenever you select your range, and the same is fixed, press the F4 key, or enter a "$"in its range. That way, if you drag this formula in a table, the values sought change, but the range is always the same, that is what we want.

 

A little practice

Well, returning one value based on the value of another cell was good for an example.

But in practice, we use the VLOOKUP function in whole tables by simply dragging the formula and propagating the corresponding rows.

Trying to be a bit clearer, suppose you have a table with the type of BTS, and must return the code of each of these types.

 

First, you simply set up the function to the first line. Considering the same example above, is shown below (note that now the index number of the column is 3, because the field is now the third in our source table).

 

Again: Remember that you are dealing with ranges, and will make 'relative' references. Even if you are getting complicated for you, at least learn that you should always make the area surveyed does not vary - for this, use the F4 key, or put the $ sign before each reference, as shown below.

 

Now, just click the little cross from the right botton corner of the cell and drag to the end of the table (you can double-click if you prefer).

 

The result, as expected is propagated, and the Code of each type of BTS is shown.

 

A very common use of VLOOKUP in Telecom is when you have a Cell ID list of acronyms and their cells. And another table from the OSS, with only the KPIs and Cell ID. With VLOOKUP, you transforms your table into something much easier to read (Customized).

 

Finishing: HLOOKUP and Languages

Finally, only two more observations.

Likewise you can use VLOOKUP to search Vertically - yes, that's where the V comes from - you can also use the HLOOKUP to search Horizontally. Naturally, you do searches on the horizontal, and when it finds, returns vertically. It sounds complicated but is not. And if you need it, you already knows it exists.

Depending on the language of your Excel, the search function changes. Stay tuned for changes, because in English you use VLOOKUP, but in Portuguese for example, uses PROCV and PROCH.

 

Conclusion

This was another brief tutorial on Excel, aimed at applications in the area of Telecom and IT. Today we learned a little about how to move rows and columns quickly and elegantly, and also how to use one of the most powerful functions of Excel - VLOOKUP.

I hope you enjoyed, please leave your comments, they are very important!