Excel for Telecom & IT - Part 1

Thursday, November 25, 2010 5:31:00 AM Categories: Excel
Rate this Content 3 Votes

There are programs that all Telecom & IT professionals (and also other areas) need to know to use. And one of the most important of them is Excel from Microsoft.

But what ends up happening is that, as well as in other programs, people end up using just the basics.

Of course, knowing the basics is often enough to accomplish our work. Except that there are certain tips, even using Excel, which are simple but very effective to increase our productivity.

Let us know some of them today.

 

The Program


I'm pretty sure you've used Excel, so there needs to be talking a lot about what it is. But in simple terms we can understand how a computer program that gathers data in tables, and allows you to manipulate data in numerous ways.

Currently, Excel and the Office suite is in its 2010 version. The former is the 2007 version. The previous versions are 2002-2003, 97 and 95.
There is a big difference between the versions and the versions 95-2003 2007 onwards.

It doesn't makes sense to show tips from older versions, from years ago. If you do not use Excel 2007 or higher, consider upgrading. In addition to this version is cleaner, has more modern interface, and brings many other advantages that we will see with time.

 

Compatibility

The new office now has a new file format - by adding an X at the end of each extension. For example, in our case of Excel, before the file format (extension) was '.xls'. Now, the Excel file is saved as '.xslx'. The same goes for Word (docx) and PowerPoint (pptx). As for Access, the format is slightly larger (.accdb).

Until recently, the compatibility between versions could be considered a disadvantage, since not all use the 2007 version. Nowadays almost everyone already has the 2007 and or 2010, and also that there are free available Viewed from the Microsoft website that has earlier versions but want to open a new version.

 

Let's go...

Well, a brief introduction, lets get down to it. You will find many sites with tips on Excel, I'll try to show that I find most interesting today.

As I think it's worth posting the tip, I publish. If you have any interesting tip, such that everyone needs to know, please share.

 

Color Scales

You can select some cells (called range), and automatically apply a preset color.

For example, suppose you have the following data table. Can you immediately see which is the highest value (offender) and the lowest?

 

At menu Home (1), choose Conditional Formatting (2) -> Color Range (3) and click on any of the options. Choose the second, for example (4).

 

See the result.

 

The data were automatically themed, and you immediately see that the worst case (red) is 245 and the best (green) is 1!

 

Paste in his new series Graph!

Imagine that you have made a simple column chart, using the Date column as Horizontal Axis and Vertical Axis as the Value column.

 

Now suppose we have to add a new series, for example New Value to the same graph. As you would. Well, most refaria all the steps you used to create the chart - I jumped because I believe that you know make a simple graph.

Well, the standard way would be to access the Select Data menu, for example by clicking the right mouse button on the chart. And the new screen, click the Add button, etc.. That is, follow the Wizard.

 

But we can do so much faster.

First, select the new column you want to add as new series in the chart. And copy the data (use the keys CONTROL C).

 


Then select the chart - click with your mouse by selecting one of the edges for example, and paste (use CTRL V).

 


Doene: the series is already in your chart (1)! And even his label is already in place (2)!

 

Functions: SUBTOTAL

The use of functions is also a great advantage of Excel. But as I said, I will not be holding me back to basics, the tips are always a little further.
To understand what we want to show how, first, consider the following table.

 

How to count the number of occurrences of GSM technology? And how to calculate an average of the Value column, when you're just showing LTE?

For this, we use the SUBTOTAL function. (Calm down, do not want to complicate anything. This function is not the most simple, but when you learn it, you see that can greatly ease your life).

First, select the entire table and apply a filter. At this point, the data can be filtered by simply clicking the arrow next to each field (1), where a window pops up check that you have already used (2).

 

So far, nothing unusual.

But let's enter the SUBTOTAL formula in any cell - to become more intuitive, type in the cell just above each field.

This formula has a first set that defines what the operation will be performed:

  • 101 AVERAGE
  • 102 COUNT
  • 103 COUNTA
  • 104 MAX
  • 105 MIN
  • 106 PRODUCT
  • 107 STDEV
  • 108 STDEVP
  • 109 SUM
  • 110 VAR
  • 111 VARP

Then, just above the Value field type the formula: '= SUBTOTAL (101, C5: C18)'. (Note that C5: C18 corresponds to the range of this sample. You must set the range for your data). We use 101 because we want the subtotal an average return.

And the cell above the Technology field, do the same, only now with 102 = count.

Ready. For the full table, you have the mean (1) which is 52.71, and the quantity or count of records (2), which is 14.


But do the following: a filter for just any technology, such as GSM. See what happens!

 

Now, for GSM, the mean is 58.6, and there are 5 records!

Well, of course, you can do it another way, for example creating a PivotTable (another time I will talk about it). But why not spice up their tables that you send to your customer with a subtotal line fields, facilitating the analysis and demonstrations?

Well folks, the idea is to move quick tips, but simple, and I think it is good to begin with, even to see your feedback in the comments, helping me learn how to write the new series of tips, for example should be more for beginners or more advanced. Slowly, but always a little bit more, I intend to go passing.

Did you like the hint, and has a similar one?

Send me that I publish his name here!

Send to contact.

 

Conclusion

This was a brief introduction to Excel, more specifically hints commands or procedures that facilitate our lives, but many people know. The idea is to be very fast, but as long as publishing new small tips that bring big results.

I hope you've enjoyed. If you have any doubts, find the answers posting your comments or email.

Till our next meeting, and remember: Your success is our success!

This tutorial is part of a set. Find more information at http://www.telecomhall.com/

RoadMap available at: http://www.telecomhall.com/roadmap.aspx