Grumpy88
Member
Hi. Getting very frustrated and would really appreciate some help from the Excel ninja gurus please!
I have an Excel 2010 spreadsheet being used as a database, which includes inter alia a range of columns containing numbers. When originally entering the data I left blank cells in these columns, because there are many instances where the same set of numbers applies to more than one row, but I didn't want to repeat the numbers in each row as there would then be double- or triple-counting of those numbers when I sum them for reporting purposes.
This system has worked well for me up until now, but recently when analysing the data using pivot tables I have discovered a weakness: when double-clicking a pivot table to drill down into the underlying data, only those rows containing the numbers are displayed. Thus the other rows to which those numbers also apply (but whose number columns I left blank so as not to double-count the same numbers) are omitted from the pivot table source data filtering. Hope that description of my problem makes sense!
In an effort to address this I am now trying to complete all the blank cells with the applicable numbers, but in text format so that they don't interfere with calculations by counting each set of numbers more than once. As typing in each number manually with an apostrophe in front of it is very labour-intensive, and creates too much opportunity for human error, I am trying to copy and paste instead.
Unfortunately I haven't found a way to get Excel to do this for me - all paste actions result in the blank cells being completed with numbers, not as text format. I have formatted the blank cells to be Text, and used Paste Special - Values, but neither makes any difference - Excel still pastes the copied data as numbers, which then throws out my calculations. I have also been unable to succeed using macros.
Can anyone please offer a solution for copying and pasting my numbers as text, so that I don't have to retype everything manually (you'd think Excel would cater for this, what with all its multitude of different paste options on offer)?
Thanks so much.
I have an Excel 2010 spreadsheet being used as a database, which includes inter alia a range of columns containing numbers. When originally entering the data I left blank cells in these columns, because there are many instances where the same set of numbers applies to more than one row, but I didn't want to repeat the numbers in each row as there would then be double- or triple-counting of those numbers when I sum them for reporting purposes.
This system has worked well for me up until now, but recently when analysing the data using pivot tables I have discovered a weakness: when double-clicking a pivot table to drill down into the underlying data, only those rows containing the numbers are displayed. Thus the other rows to which those numbers also apply (but whose number columns I left blank so as not to double-count the same numbers) are omitted from the pivot table source data filtering. Hope that description of my problem makes sense!
In an effort to address this I am now trying to complete all the blank cells with the applicable numbers, but in text format so that they don't interfere with calculations by counting each set of numbers more than once. As typing in each number manually with an apostrophe in front of it is very labour-intensive, and creates too much opportunity for human error, I am trying to copy and paste instead.
Unfortunately I haven't found a way to get Excel to do this for me - all paste actions result in the blank cells being completed with numbers, not as text format. I have formatted the blank cells to be Text, and used Paste Special - Values, but neither makes any difference - Excel still pastes the copied data as numbers, which then throws out my calculations. I have also been unable to succeed using macros.
Can anyone please offer a solution for copying and pasting my numbers as text, so that I don't have to retype everything manually (you'd think Excel would cater for this, what with all its multitude of different paste options on offer)?
Thanks so much.