Telecom to Excel - Part 3 (connecting chart null points, using custom lists and finding unique records)

Wednesday, August 24, 2011 1:35:00 PM Categories: Excel
Rate this Content 0 Votes

Let's continue learning a few more tips on using Excel, from Microsoft.

 

 

These are quick tips, but applied in situations that we find in our work. By always learning new tips, you can not only make everyday tasks faster and simpler, but can also become an increasingly differentiated professional.

So let start this brief tutorial today.

Note: All telecomHall articles are originally written in Portuguese. Following we translate to English and Spanish. As our time is short, maybe you find some typos (sometimes we just use the automatic translator, with only a final and 'quick' review). We apologize and we have an understanding of our effort. If you want to contribute translating / correcting of these languages, or even creating and publishing your tutorials, please contact us: contact.

 

Connecting with lines for all points of a Chart (that has empty values!)

Let's start with the following situation: Imagine you have a table with Traffic data per month for a given cell, as in the table below.

 

Note that we do not have data for the months May to July, but we do have August data.

If we create a chart to the table above, the chart by default would looks like this:

 

Not very professional, and does not good to see data evolution. Okay, to solve, we create a chart with the 'markers' on the points?

 

Okay, a little bit better. But still does not give clear idea of evolution, don't You agree?

Would not it be better if we could fill the empty values? Yes: we can connect the data points with lines!

To do this, first select the chart. Then, access the Menu: Chart Tool -> Design (1) -> Select Data (2). In the dialog box, click 'Hidden and Empty Cells'(3).

 

And in the new dialog box, select the option 'Connect data points with line' (1)!

 

There: our graph is much more 'real'!

 

With the chart that we use in this example, you could see the idea.

However, this tip is much more interesting when we have several periods, and lack of data in many of these periods.

For example, see how is a chart with 3 series, and several missing data points before and after using this tip.



It is better to expose the trend than just a bunch of lost dashes, dots or crosses, isn't it?

 

Custom Lists

Now imagine the following situation: you need to add the letters of the alphabet at the end of any list in Excel. This is easy because we have the original list, and we can create a another automatically for the alphabet?

Well, not quite. Try to create a list with the alphabet, for example by first typing the 'A, B and C', and then simply dragging it with the mouse.

 

Excel DOES NOT automatically fills it!

But wait, there's a solution. We'll teach you. For this we need to add a 'Custom List' - a list that Excel will seek, when You call.

First, in Excel, type your wanted list - in our case, as shown below.

 

Then click the "Office Button" (1), and then the 'Excel Options' (2).

 

Next, choose 'Popular' (1), and click 'Edit Custom Lists ... '(2).

 

The new window (Custom Lists) allows import the data from our New List (1). To download, simply click the 'Import' (2).

 

Note: Note that the data range are already selected, as previously informed. Nothing prevents you from clicking the button (3) next to the 'Import' button, and change the data source. If you prefer, you can also type your list in the blank area (4), and click the 'Add' button (5).

 

To test, type 'A, B and C' in any Excel cell (1), and then drag three more cells with the mouse cells (2). The letters 'D, E and F' are automatically filled in (3)!

 

Note 1: No matter if the letters are uppercase or lowercase in the new added custom list. Explaining better: test for the letters 'a, b' - see that Excel auto fills it!

And how the numbers 1,2,3 ...? Well, Excel already have this list 'internally' added - you don't need to do anything!

 

Finding Unique Records in a List/Range

Finishing for today, suppose you have a table with traffic data for your network ('Date', 'cellname' and 'Traffic [Erlangs]' among others).

 

Suppose also that someone ask": 'What are the unique list of cells that are present in this report?'

We know that applying a filter and clicking the arrow in the corresponding field (1) - cellname - we have such a listing (2). But how to 'transport' this information to a place where we can use it?

 

There are some ways to do this quickly and accurately using Excel - some of these ways is not commonly used by most users. Let's see them.

The first one is more a 'Workaround' than a specific solution to this problem.

Select the entire table - or at least one cell of it - where the data are (1). Next, open the Menu: Insert (2) -> PivotTable (3) -> PivotTable (4).

 

In the new dialog box, note that the 'Table / Range' is already properly selected with our data (1). Let also selected the option to create a 'PivotTable' in a new 'Worksheet' (2), and click OK (3).

 

Thus, our PivotTable is created. Now, do the following: drag the field 'cellname' (1) for the area 'Row Labels' (2) of the PivotTable.

 

Ready! The data are available - and only with unique values. Just select the listing, copy (CTRL + C) and paste (CTRL + V) wherever we want.

 

Okay, this is a way. But there is a faster and more direct: using 'Remove Duplicates'.

Just select the listing with details of where you want to remove duplicate records. In this case, we can simply select the column where is our field 'cellname' (1), and go to Data Menu (2), and click 'Remove Duplicates' (3).

 

Depending on your list, Excel will ask if you want to expand the selection to the data next to your current selection (range). As we only want to list the field we have selected, check the box 'Continue with current selection ...' (1) and click 'Remove Duplicates ...' (2).

 

In the next dialog, make sure the option 'My data has headers' (1) is checked - if your listing have or do not have a header field. Then, simply click 'OK' (2).

 

As a result, we have the list with unique records, as expected.

 

Note: Note that when using 'Remove Duplicates', we get our expected result, but our data is changed. Then you must copy them to a desired location, and then exit without saving the document.

A third option to perform this action is through the Advanced Filter feature. Again, select the data to generate a list of unique records.

Then go to Menu: Data (1) -> 'Sort & Filter' (2) -> Advanced Filter (3).

 

In the new dialog box, choose Action option to 'Copy to another location' (1), so our original data are not changed, and we have the final list in another place specified by us. But note that the data - List Range - are selected by default as the whole table. Adjust again to only our desired column (2). Next, enter the location where the result of the filter should be presented - 'Copy to' (3). Finally, select the "Unique records only' (4) - thus ensuring that our final list has only single records.

 

Clicking the OK button, we have the result.

 

Well, those were three ways to get a list with unique records based on an original listing. Each way has its own pros and cons, is up to you to choose which one to use.

Particularly, we like to use PivotTable to achieve this result, because it allows us to something else, such as counting how many instances of duplicate (1) for each record there!

 

That's it! Keep watching, and becoming more expert in using this amazing tool that is Excel. Tutorial by tutorial, we present here the best tips for you!

 

Download

To download files from this tutorial, click here.

Note: The Hunter SDK is a set of tools designed and demonstrated in telecomHall - including Macros and Codes. The Hunter SDK is only sent to telecomHall Donors, as a thank you for the contribution they made and recognition of our dedicated effort. Click here if you want to know more.

 

Conclusion

This was another tutorial for Excel series in telecomHall, with task applied in the Telecom and IT field. We have seen how to create lines connecting points in graphs with some missing data points to improve visualization. We also saw how to use the Custom Lists - eg 'teaching' the alphabet to Excel, and we also saw how to find unique records in a list or range.

We hope you enjoyed. Two last things: if the tip was interesting to you, share with your friends. Also, leave your comments below - they are very important, and determine the future to be published by us.

Thank you!