Excel for Telecom & IT - Part 4 (Hiding Cell Values - Force Understand Data Type - Repeat Labels PivotTable - Fill Blank Cells in Column)

Posted by leopedrini Sunday, October 13, 2013 9:09:00 AM Categories: Excel
Rate this Content 2 Votes

Let's talk again about Microsoft Excel software, so important in the telecommunications work (and also to numerous other areas) that often we heard someone say that he should be one of the subjects of the University.

Even if you don't totally agree with the above statement, you must at least know that Excel really helps our work, and that is why it is so used.

 

 

The tips that we present today, and in all tutorials of this series can also be seen as 'tricks' that make us have a better use of this tool, and we simply do not know (yet). So let's meet a few more tips?

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.

 

Hiding the contents of a cell

In the tutorial on 'Propagation Delay and Timing Advance', you saw that an interesting form of analysis is by viewing the data in a table, but applying a 'conditional formatting' coloring cells according to a range of colors.

 

In this kind of analysis, where we highlight the colors and not the content or values of the cells, a good tip is to 'hide' these values.

So, suppose a table with random data, where you apply conditional formatting. The normal result is something like the one shown below.

 

No doubt the shape above already gives us a much better view than simply observing the plain values (as shown below).

 

But the result may be even better: If you simply 'hid' the values. You may think: an option to this would put the numbers with a very weak color. Well, kinda helps, but the result is still not ideal. See for example, the worksheet with conditional formatting, and with the values with the 'white' text color.

 

Ok, another way would be set the text of the content of each cell to be equal to the same fill color. Okay, that worked. But that would be a huge job for sure. And in addition, when the format changed, the numbers would appear. (In the following example, we demonstrate with just 4 cells).

 

Now that you've figured out what we want to do, be aware that there is a very simple way to achieve this: simply use a specific function of Excel, which formats the 'Negative', 'Zero' and 'Positive' values.

Namely, we can assign the format "" (empty) to all the criteria: ""; ""; "". To be even simpler, we can simply type ";;;".

To do this, select the range to be formatted (1), click with the right mouse button and choose 'Format Cells' (2).

 

In the cell formatting screen, in the 'Number' tab (1) choose a 'Custom' Category (2) and under 'Type' type ';;;' (3).

 

And the result: the values are 'hidden', no matter what the color or condition applied to our range!

 

You can use this trick in any other type of scenario where you wish to only highlight the colors, bars or whatever 'attribute' related to the value.

In addition, you can use to highlight negative numbers, Zero and positive numbers in a range – in fact, that's the main reason of this function.

 

Note: to format numbers as above, select the desired range, and format as '[Red]General;[Blue]General;[Black]General'.

 

Add 0 or Multiply by 1 the values in a Range

When we type some content in a range of Excel cells, cells have General formatting. And in this case, the values are understood as its default type. In the example below, we typed a sequence of numbers (1), and if we see the formatting we see that it's 'General' (2).

 

When pasting these 'pure' values in other ranges with some formatting already assigned, the result is as expected: the data remain correct or 'consistent'. The following is the same data (pasted as value) in a range formatted as text (1), another number (2) and a third formatted as date (3).

 

However many times we came across a set of values in Excel with different formatting, especially when you work with data from different sources. In the following example, we have a range with several numbers, but not all with the correct formatting, or with the type of content that Excel can 'understand' . For example, if you for example puts a signal ' before the typed value, this cell is forced to understand as text, even if it's Number or date.

In the range below we see this problem (let's assume that came from an unknown source). Excel does not assign the same formatting for the entire set.

 

Even if you select the range, and paste the contents as value, the result is not as expected.

 

To resolve this problem, we can use a trick: we can paste values in such a way that all fields are forced 'Updated'.

In a cell, we typed the number '1', then copy this value 'CTRL' + 'C' (1). Select the range where we want to 'update' values (2), and with the right mouse button we choose 'Paste Special...' (3).

 

In the 'Paste Special' window we choose paste 'Values' (1) and operation as 'Multiplication' (2). By clicking on the 'OK' button (3) we have the result as it should be (4).

 

This kind of action can be used both by multiplying the values of the Range by '1' (one), as well as Adding '0' (zero) to the range. The concept is the same: trying to 'force' Excel to see that all fields have a particular data type. This operation can be made also with Dates.

 

Repeating empty fields in a column

We have no doubt that the Excel Pivottable is a fantastic resource, and its results are amazing. However there are some situations where this type of resource must be 'improved' .

To illustrate this, suppose a range (list) with CELL and SITE, HOUR and CS Traffic. Applying a PivotTable to it (Select Range, and access the Menu Insert-> Pivot Table), we can easily create some reports, such as the sum of traffic for 'all' hours.

 

To do this, we simply uncheck the hour, and everything will be added, grouped by SITE and CELL. The result, as expected, shows us the Total traffic 'sum' for each cell.

 

However, the way they are presented has a little problem, especially if we want to do some type of filter to the data. Or rather, if we need to do a search of type 'VLOOKUP' in the column where the fields are 'missing'.

Ok, started getting complicated...

It's easier to understand this if we copy the PivotTable data, and paste as text in another worksheet. See 3 data columns, in fact here presented in 2 columns.

 

An option to minimize this problem, is to choose the PivotTable options ' (1), and on the 'Display' tab (2) check the option 'Classic Pivottable Layout' (3).

 

That way, when we select the table again, and paste the data (as a value) in another worksheet, we have the result below. At least now we have 3 columns.

 

In the table pasted as text above, we had the Total options Selected (lines in yellow in the figure above). We're back in our PivotTable options, and now we uncheck the options 'Show Total for rows' (1) and 'Show Total for columns' (2). In addition, we click on column showing subtotals, and uncheck this option (3).

 

After all this 'gymnastics', we paste again the data as values, and get a new table - and that's still not what we seek: we still have blank cells in an Excel column.

 

And then, what to do to solve?

Unfortunately, we have no way to solve it through Menus in Microsoft Excel 2007: we only it in microsoft Excel 2010 onwards. For users of 2010 onwards, simply right-click the PivotTable field, and choose 'Settings' field, and select the new option: 'Repeat item labels' (1).

Note that in Excel 2007, we don't have that same option.

 

But if you're still an Excel 2007 user, you can see how to solve the above problem in the next tip.

 

Filling blank cells in a column in Excel

Let's learn how to fill blank cells in an Excel column - like cells in red in the example below.

 

First, select the range where there are blank cells (1). Then, go to the menu 'Home' -> 'Find & Select' (2)-> 'Go To Special... ' (3). in the new window, select 'Blanks' (4) - because we want to select blank cells. Finally, click the 'OK' button (5).

 

Our blank cells were then selected in a range, and now we can create a 'Formula' for each one of them. In this case, a simple formula to 'copy above cell values'.

With the range still selected, type = ' (1) in the first blank cell, and then type the 'Up Arrow' (2).

 

Attention, important: now, instead of typing 'ENTER', type 'CTRL' + 'ENTER'. And done, all of our blank cells were filled as we wanted (1).

 

Note: If you just hit 'ENTER', the formula would be applied only to this cell. But when we typed 'CTRL' + 'ENTER' the formula is applied throughout the range selected – which in our case are all blank cells.

But note that our work is not yet over. That's because if we do some filters or sorts, we're in trouble. To resolve definitely need paste the formulas to values.

So, with the previous range still selected, press 'CTRL' + 'C' to copy (1), and via the Menu 'Paste' (2) -> 'Paste Special ... ' (3) click on 'Values' (4) and then the 'OK' button (5) to paste the values in places where the formulas are.

 

In short what we did: first we select our range of blank cells (with the 'Go To Special...'). And then apply a formula in all these cells - to copy the value of the above cell. It may not have been entirely clear to you, but this is a very important action in Excel – using the 'Go To Special' to select a certain range, and the 'CTRL' + 'ENTER' apply formulas to this range. Then, use the 'Paste Special - Values' to replace these new formulas with their values.

Fortunately, all this trouble can be avoided in Office 2010 onwards, as we mentioned earlier. Anyway, if you need to use in Office 2007, now I know how!

 

Download

To download the files from this tutorial, click here.

Note: the Hunter SDK is a set of tools created and demonstrated at telecomHall-including Macros and codes, in the Hunter. The Hunter SDK only is sent to Employees, Donors as a thank you for your contribution and recognition of dedicated effort. Click here if you want to know more.

 

Conclusion

This was another tutorial of the Excel series, aimed for applications in the area of Telecom and IT. Today we saw how to 'hide' the contents of a cell, helpful tip to improve the visualization of certain types of reports.

We also saw how to 'force' Excel to understand the given data type of a range, by adding '0' or multiplying by '1' the values on the range.

We still saw how to improve the presentation of data from a PivotTable in Excel 2007 by learning how to fill blank cells in a range with the values of a formula – in our case, that was the given the cell above.

We hope you have enjoyed. If possible, leave your comments below: they are very important to us. Thank you very much!