www.excelrange.com

Using ASCII codes it is possible to find or replace invisible characters like newlines in Excel worksheet. E.g. when needed to find newlines and replace them with something else, you can use ALT+010 in the search field.

The same technique is usable for changing certain characters into new lines (inserting newlines instead of comas or semicolons e.g.).

Newlines are created in Excel while typing in the cell and pressing ALT+Enter.

Excel time saving

While working with spreadsheet data, it’s common to face situations where you have to manually reorder or transform data. Macros are of good help then. I’ll share an example of a quite common case where simple macro can help save a lot of time.

Case: the spreadsheet contains manually entered and badly structured data. You need to paste together the data from various cells, but you can’t do it with formulas because every situation is slightly different (contains different number of rows e.g.). Basically, you need a tool that takes the contents of currently selected cells, concatenates the values and pastes the outcome somewhere.

Problem and the desired outcome is depicted on the following screenshot.

As a solution, you can use a simple macro that takes the contents of the selection, concatenates it using newlines between values and pastes the outcome to the first selected cell. The code that does this is on the following screenshot. It uses Selection.Cells property that refers to the currently selected range of cells.

Excel macros, time saving

• formula bar in Excel 2008 is disturbing. On laptop screen it gets lost behind other windows. 2004 version was better.
• no Data Analysis pack
• no VBA macro support

Excel 2008 for Mac flaws and problems vol 1

Excel excel 2008, mac

Microsoft obviously doesn’t like the Mac platform. In Excel 2008, there are some really terrible functionality-cuts that will disturb every professional Excel user. And will force you to use Office 2004 or the PC version with a virtual PC (Parallels in my case).

I’ll collect here my findings of the serious problems I’ve found, bit by bit. Read more…

Excel excel 2008, mac

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. Read more…

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. Read more…

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. Read more…

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? Read more…

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. Read more…

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. Read more…

Excel time saving