- blog (13)
- game development (61)
- Games (9)
- games related (9)
- MS Office (7)
- PHP (3)
- Tutorial (8)
- Intermediate (1)
- Tutorial Series (15)
- Uncategorized (1)
Tag Archives: Excel
Coming in with a lot of (continuous numerical) data, I struggled to find a neat chart type to display it. I went for a contour plot but it needs the data sorted in rows and columns while my data consisted of 5000 rows and two columns. Manual ordering would have been possible but to work intensive (5000 rows!). And since
COUNTIF does not come with the ability two compare two criteria (or I just did not find it) I figured out a pretty good workaround. By the way, this works in Excel2007 but should work in 2003 and 2010 too.
The task was to use text files that are created by an external program on a daily basis to create a workbook that contains those files’ data in monthly spreadsheets. The good thing is, the text files were named ‘DATAYYMMDD’, so there is a keyword (“DATA”) and the actual date in year, month and day form.
Let’s assume, you got 12 worksheets named with year and month. Each sheet contains several values for every day. To put them all together I recommend a combination of the Excel functions CONCATENATE and INDIRECT (the real dynamic duo if there wasn’t one before).
Setting up a workbook
Open Excel and create a blank workbook. The default settings will create three Worksheets, that’s enough. My personal settings only create one sheet, the others are inserted by ‘Insert->Worksheet’. For the beginning we need two sheets. These are renamed (rightclick on tab->Rename) to ‘budget’ and (for actual reasons) ‘2009’. Then go to ‘File->Save as…’ and save it.
Within MS Excel one can adjust the height of a row by dragging the boundary below the row heading until the row is the wanted height or double-click the boundary to get an automatically adjusted height that fits the content.
This is true for single column, non-merged cells, whereas the first method also applies to merged cells. But imagine a 300 row sheet. I would not want to drag every row to a certain height after changing the font size. One could say: “Alright, I’ll just select the whole sheet by clicking the field left to A and above 1, drag one row to a height that fits the maximum height.” Lots of empty spaces. Does not look good.
Anyway, in writing the document just add a simple step.