Data Entry Efficiency Help

[clw496]

Hello, I'm looking for suggestions on improving navigation in large spreadsheets.

I've got a table that looks something like this, except it has a couple hundred columns and rows
NappingExample.png

I'm entering in the 'descriptor' data, which is freely generated for each Judge (a 1 represents that the judge used the descriptor for that product). For example, Judge1 for Product 1 can say 'sweet', for Product 2 can say 'pineapple' and 'cherry'. As I enter the descriptor data for each judge, I am adding descriptor columns for new descriptors and placing 1's.

My problem is that I have many judges, many products, and many descriptors, so as I enter data I spend more time scrolling to place a 1 under the descriptors they used than anything else. I have the top descriptor row frozen so that I can still see the descriptors and they are sorted alphabetically, but that only helps so much. Can anyone think of a way to make navigation easier for entering the data? I would love a way to be able to skip over to a particular descriptor or descriptor starting with a particular letter.

Any suggestions would be appreciated,
Thank you!!

[TMS]

I would select cell E2 and choose View | Freeze Panes | Freeze Panes

Then you can switch on AutoFilter which will allow you to select Judges and/or Products and/or X and Y, whatever they are.

That would narrow down the data you have to view and it would retain the header row.

Or, you could use Freeze Panes but convert your data to a Structured Table (Insert | Table). That would give you the filtering but also allow you to easily sort the data, if necessary.

For future reference, if you post a picture, we can only look and admire, and offer suggestions. If you post an actual sample workbook with some typical data, which you obviously have, then we can apply and test any proposed solutions and variations on a theme.

Regards, TMS

[clw496]

Thanks for the suggestions. I currently have the panes frozen, which helps a bit but my problem is more with navigating across columns. I haven't tried converting it to an actual table though.

I can't share my actual data, but I've made a sample version of my sheet. Judge 1 and 2 have data entered, but I've left judge 3 blank. The way I'm entering in the data currently, I would go to the first product for Judge 3 rep 1, and use the arrow keys to navigate over to a descriptor they used. I would insert a column for it if it hasn't been used yet, or place the "1".

Overall, I will have 1,800 rows to enter (with about 4 descriptors per line), so you can see why I am looking for a quicker way to do this...


ExampleSpreadsheet.xlsx

[TMS]

OK, this is more about ideas and food for thought. I would remove all the subtotal rows and put them on a separate Summary sheet. Then I'd convert the data to a Structured Table. And, to make navigation simpler, I'd add some additional blank columns for each letter of the alphabet ... then you can group the columns for each letter.

The reason for moving the subtotals from the sheet is because maintaining them will be a Royal PiTA as they are all different rows. You may notice that you've actually missed the top couple of rows, at least in the example workbook.

The table allows you to filter and sort the rows. The grouping allows you to hide or unhide the columns you want to focus on.

On the Summary sheet, I've used INDIRECT to simplify the formulae. However, because there are so many formulae, that may make the workbook a little slow. Maybe need to suck it and see.

But, rather than manually manage the summary/totals, you could insert one or more Pivot Tables. In the attached example, I've set one up that just looks at the letter A.

Hope this helps you decide on an approach

[clw496]

!!!! You are wonderful!!!!!

The grouping on the table is P.E.R.F.E.C.T. Just what I was looking for. I am going to be thanking you for the next week while I enter this data, as it will probably take me half the time. :D
The Pivot Table also looks like a great idea - I've only just started to learn how they work, but I'll take a look at what you did. Thank you for the great advice! Other students researchers where I'm working are doing similar data entry, so I'll be sure to pass your tips and tricks on to them. I was just waiting until all the data was entered to check and fill in the summary rows, but I will take your advice to separate them as well.

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.