www.excelrange.com

Microsoft Excel’s styling capabilities have improved over the time. But there are still lackings on some areas. One thing I personally miss is the ability to attach styles to Pivot table items. For example you have in one table the sums of revenues and sums of quantities – and you wish the revenues to be formatted differently. Of course, you can manually change the formatting of each revenue cell, but this is not a very modern way to handle it – considering we use styles for such formatting tasks in MS Office and other programs nowadays.

I have a little tip for those of you who face the same problem – you can still attach different color to different Pivot table items. It is possible to use custom formatting to define the color of the Pivot table data item. This way all data of the same kind will be formatted with the same font color – resistant to table refreshes, size changes and field arrangements. And most importantly – you can change the color instantly from one place.

Field format is applied by right-clicking on the field name: Field settings -> Number

Then you should apply ‘Custom’ number formatting and define color format as shown on the screenshot below. This makes the text blue:

Below is a table of milk production data. One data item is made blue to distinguish it from the rest of the table:

Märt Parker Excel cell format, data visualization, Pivot table, time saving

It’s quite common that the calculation adds precise numbers which are formatted to be less precise. It might also happen that due to rounding, the formatted result seems mathematically incorrect on screen.

Example:

The last red number seems incorrect, because 12,8 + 12,8 = 25,6

Usually this is OK, as from the source data point of view, this is mathematically correct.  But on some rare cases you want to add up numbers as they are displayed and you want the result to be 25,6. To let Excel do the math at the displayed precision level go to:

On Excel 2007: Excel Options > Advanced, scroll down to When calculating this workbook

On Excel Mac 2008: Excel -> Preferences -> Calculation

And check the field ‘Set precision as displayed‘

This should be used with caution as Excel changes the numbers in cells permanently.

Märt Parker Excel cell format, formulas, rounding numbers

Here is one tip for improving the readability of larger data tables – use in-cell graphics. This is based on using ordinary characters as graphic elements. It is possible to repeat the characters using Excel REPT() function.

Below is a statistical table from Eurostat that is tuned with in-cell graphics.

Tip 1: You can apply calculations to second parameter to fit the result into desired space (in this case, the numerical value is divided by 10)

Tip 2: Try experimenting with different characters for the repetition, like “-”, “o” as the first parameter for the function. You can also apply some other character at the end of the formula with “&”+[end character]

Excel in-cell graphics for improved readability

Märt Parker Excel charting, data visualization, formulas, function

Excel has a very uncomfortable bug – if you create a Pivot table in Excel 2007, then it will be “readonly” in earlier versions. I.e you can’t apply any new filters or re-arrange data fields. The only picture you see is like a static snapshot of the moment when it was last saved in Excel 2007. Even saving in 2003 compatibility mode will not help. Somehow the creation algorithm of a Pivot table is different in Excel 2007.

What to do when you need to have a Pivot table which can be used for Excel 2003 and 2007 users at the same time? The only solution I know is to first create a Pivot table in Excel 2003. After that you can edit the Pivot in 2007, save modifications and it still works in both versions of Excel. So it is useful to have an older version of Excel available besides 2007.

Märt Parker Excel Excel 2003, Excel 2007, Pivot table

Large Excel spreadsheets are very vulnerable to mistakes in formulas. To reduce the probability of errors, good organization and structuring of data is a must.

Subtotal() function is one of the features that can help you to organize the spreadsheets better and prevent messy calculation logics. Novice Excel users often use SUM() function in places where SUBTOTAL() would be more appropriate. For large tables this practice can produce unmanagable files and hard to find calculation errors.

SUBTOTAL is meant to calculate subtotals or summary rows in the middle of the data table. The important distinction between Subtotal and Sum is that Subtotal does not sum cells which contain another use of Subtotal. This prevents multiple sums.

Bad practice		Better solution
		*Subtotals of categories are calculated with SUBTOTAL as well

Using Subtotal instead of Sum() in formulas has the advantage, if you need to calculate over large number of subtotals and the table is long.

Be careful – if somebody else modifies the table and accidentally adds a row with SUM() function into the range that you sum with subtotal, then the result will be wrong.

Märt Parker Excel formulas, function

What will you do, if you have an output from a Pivot table and you need to use it as a source for another pivot table? Or you simply have gaps in the table that you need to fill with information above (or some other side). Not knowing the right approach might leave you the only option to change the cells manually – which would be a terrible waste of time for larger tables.

As an example – the left table below is not suitable to use in data analysis – you can’t even filter rows by ‘Category’ if you would like to:

Convert  to >>

Fortunatelly, there is an elegant time-saving solution to do this automatically. The logic behind the solution is that you can use the Excel command to select only empty cells and then change them all at once.

The steps are the following:

1. Choose the area (column) you need to fill – the first column in our example
2. Press Ctrl+G or choose from the menu Edit > Go To…
3. Select ‘Blanks’ and click OK. The empty cells of the first column should now be selected.
4. Write formula – type equation sign (=) and press UP arrow. By doing this, every selected cell gets the value directly above.
5. Press Ctrl+Enter. Holding Ctrl tells Excel to change all the active cells at once.
6. One more thing – you’ll have to convert the formulas to static values to quarantee that the values will stay the same even after sorting and other transformations. Use ‘Copy’ and Paste Special -> Values. Now you’re done.

Märt Parker Excel time saving

Excel has built-in data tables and charts. There are situations when you have a large nicely formatted table and you just need to add there little diagrams to envision the trend. Regular charts are too uncomfortable to use, because you would have to give up all the formatting of the excisting data table and create everything anew in the chart. The solution is to use sparklines charts.

So what’s a sparkline? It’s a tiny graphic near the table column or row that helps the reader to grasp the meaning of the numbers quickly.

Sparklines chart

How to make sparkline charts in Excel? This is not a standard Excel functionality, but you can use an Excel add-in. Those are actually macros which create an Excel chart without all the unnecessary attributes (axes, units etc.)

Here is the list of add-in’s from the internet:

TinyGraphs – a free add-in, which can be used to make graphs for a row of data. Open-source macro and installable add-in both available.

http://www.spreadsheetml.com/products.html

SparkMaker – it is not free, but has a free trial. Possibility to make sparklines in Excel, PowerPoint, Word.

http://www.bissantz.com/sparkmaker/index_en.asp

MicroCharts – commercial software

Märt Parker Excel charting, data visualization