1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

disappointing Office/Excel 2010 - Data Validation

Discussion in 'The Lounge' started by Stephan, Mar 11, 2017.

  1. Stephan

    Stephan Member

    Excel 2010, looks promising, at a glance Interactive/Dynamic Data Validation to display Dynamic Tables/Charts, looks like a simply solution to bespoke/custom Formula/VB edits.

    How on use of Pivot Tables & Slicers, they are incoherent and useless, intentionally, not just to waste your time, to waste the rest of time, and get a newer version of Excel & see if it's any better!

    Excel is for collating & summarising data, if Data Validation (Categories) and Dynamic Tables/Charts (Results) worked as intended via Pivot/Slicers then Office/XL2010 would have satisfactory. Because if PivotTableSlicers are what you do to get what you want in Results, then the concept id good, but the approach is obviously to appear uncoordinated, chaotic & clumsy, obvious rouses for more Mcse courses, add-ins, outsourced alternatives, and newer versions of Office, all of which wud be un-necessary if Data Validation was as simple & uncomplicated as it can be, perhaps it's better in Excel2013/16, if not will Excel 2020 be any better? Or does the annual subscription Office365 Data Validation does that even work? Of course all the Office Packages @ 1st glance appear Glossy, Professional & packed with features, but if you can't 'Summarise' concisely with it, then it's not worth upgrading from XL 2003, hence what use is it, not alot, but it does make a great converter from 2010 > 2003!
    Last edited: Mar 11, 2017
  2. Stephan

    Stephan Member

    Pivot Table (Dynamic Table) > Slicers (Combo DV Connected) > Dynamic Chart.

    Above is MENU driven approach 'methodology' in theory for SUMMARISING already collated data in table via Formula/VB in XL2010. However in use it is chatoic/useless, surprising something simple as AutoFilter ComboBoxes in seperate Dynamic Table is awkward to simulate?
    I doubt they're been working on menu approaches for 15-20years, they're just staging newer versions of Office/XL, hence hoping users will presume its their lack of understanding and not the total lack of intuitiveness for insert/refining Pivots.

    1st step being for own menu summarised data to insert Pivot Table of range already pre-selected or enter range, however immeadiately the dynamic table provided doesn't accurately display the results intended or coherently, hence if 1st step if contrived and useless, then all that follows will be inadequate!

    Since use of Pivot Tables appears to be duplicate of existing table, but with AutoFilter persons who are unfamiliar with them, will perceive not of use to them, as AutoFilter has more clarity for use & implementation, and Slicers just aren't apparent in menu or options via Pivots. Hence learning users are highly unlikely to stumple upon a satisifactory interactive/dynamic solution to static data.

    Doubt Excel professionals use XL2010, perhaps XL2013/2016 does actually offer coherent pivot/slicers? Will have to try the programs, but do developers really want to offer a coherent solution that could potentially standardise data collation/summary, and leading to many more persons developing sophisticated spreadsheet designs, instead of wasting their time trying to refine initial pivot and fiddling round with slicer connections.

    Hence on this premise, doubt anybody unfamiliar with Formula/VB, with XL2010 or previous can produce anything interactive/dynamic in XL!

    Because if you can't interactively/dynamically summarise from static data, this will prevent you from establishing useful approaches, and wasting years many years, just because to get started on any individual project, involves lots of unneccessary chores to repeated over & over again, they are inconsistently, not what you want.

    Hence they won't be the AI designers of Tmrw amassing new approaches for reinserting consciousness from 1 person into another, then once figured out how all that 'works' horray...., such as having to contemplate how to store/interpret/transfer consciousness/memories, then the consideration of transfer into what, well...., not what, a human body..... where woud u get1.... & u can't do that... or the Amazon/Tarzan/

    So XL users won't be the nxt StarGate RA, Dax DeepSpace9, Xmen Acopolype, RoboCop, Avengers, Aliens, and many many more, these are all films that have this theme of persons consciousness in another persons body.
    Very little considerations where the host came from, doubt with their consent, u cud get a Tarzan from the Amazon.... yer goto another country we'll make you a new citizen etc, drink taste funny?
    The Matrix.... here's a container, do you still need this, and if this is gone is that the connection? or 2 consciousnesses that are seperate? do u still need this? ie imagine that having a new life in a new body, what about the old1, do a darth vader with it? of course this sounds like science fiction, it won't be, sum billionaires will have done it numerous times.
  3. bobhc

    bobhc Excel Ninja

    I was reading your tirade but gave up when you lost it and the grammar and spelling descended to mobile phone text.
  4. Stephan

    Stephan Member

    Mobile phones aren't best for posts, spell chk did occur but edit disappeared!

    Holistic point being if you spend all your time 'trying' to summarise & analyse your data, then what the data trend represents becomes less of an immediate consideration, it becomes secondary, hence search and consideration for technique with thoughts of training<>learning more is necessary, obvious rouse to buy the 'next' version of Office.

    Menu driven data summarising via Data Validation shouldn't be complicated to insert & use, but it is in XL2010, look at how results are conveyed, not how you wanted or imagined is it?

    Since it's just categories, AutoFilter categories derivative via Defined Names is straightforward, but for some reason processing speed is affected, I very much doubt, its strenuous for the laptop, 1 could theorise a constraint in the program to make it appear to be resource intensive for those who have constructed their own interactive/dynamic dashs with data validation.

    After all did you really think it took Program Developers 15-20 years to think of Data Validation Menu Driven? Of course not it would have been imminently obvious on construction of AutoFilter categories, and consecutive column categories, simple.

    Hence interestingly can OFFICE/XL 2013 or OFFICE/XL 2016, coherently insert Pivot Tables & Slicers, without having the need to adjust several options for even the most basic table with few fields?
  5. bobhc

    bobhc Excel Ninja


    You are living in the UK so medical treatment is free. I think you really need to get some help with your head. your thought processes must resemble Clapham junction.

    or lay of the drugs.

  6. Stephan

    Stephan Member

    Ooh derogatory remarks, did you think persons who aren’t program developers are all naïve & gullible! Gyms aren't free in the UK, don't believe contrived news.

    XL2010 Program through its lack of intuitiveness implies more familiarization and/or knowledge is necessary, so blantantly obvious from something considered a standard, which it would be if menu driven data validation summaries were simple and powerful for any user, it doesn’t have to be complicated to be good!

    ‘I know you're out there. I can feel you now. I know that you're afraid... you're afraid of us. You're afraid of change. I don't know the future. I didn't come here to tell you how this is going to end.’ The Matrix - Neo

    It is obvious, data collated can be described as a spreadsheet/database with rows & columns, with differing structures to categorise, afterall how else would data be captured/collated! Notepad without grid referencing! Obviously not, CSV, comma separated to exclude blanks, great idea…..

    DATABASES should WORK FOR YOU, you shouldn’t have to WORK FOR THE DATABASE!


    Yes of course datasets are different, but defining categories are normally the first few fields, the rest are normally characteristics, the real question is determining which categories are applicable for Data Validation and the characteristics within the Dynamic Table/Charts!

    Sound familiar? It was hinted in a popular film:
    The Matrix - Cypher

    Hence it really shouldn’t take several years or decades to reach a consensus of good technical critique of what would work best depending on layout & content of data!


    Pivot Tables (Dynamic Tables) > Slicers (Data Validation). 15-20 years in progress?

    If they worked as intended they’d be a reasonable time saver, but not on XL2010, is 13 or 16 better? PAIRING of ADJACENT CATEGORIES is a simple concept to search from CHOICES AVAILABLE to SUMMARISE DATA in DYNAMIC TABLES/CHARTS, and because the lack of coherence isn’t obvious to most then they wouldn’t query it, thinking it’s just an old version.

    Now obviously you can’t make Programs appear faulty, hence various versions of Office surprisingly look fully featured except for any real true manipulation features for categorising/summarising/analyzing data through well defined Data Validation technique, defined names can be lengthy if many categories exist, and few techniques have automation for this, apart from my attached Generic Data Validation Technique.


    Oddly how long it takes a person to scroll through the data shouldn’t make much difference to a computer processor & XL, yes the greater the cell ranges, the greater the number of grid references to search & calculations to perform, but compared to Games & Videos surely XL calcs use hardly any memory resources you’d imagine.


    This is just to make persons contemplate whether there are other approaches to theirs, even when they have excellent clarity! ie to make them think it’s not the Program it’s themselves!

    ‘A world without rules and controls, without borders or boundaries. A world where anything is possible. Where we go from there is a choice I leave to you.’. The Matrix - Neo

    Attached Files:

  7. Marc L

    Marc L Excel Ninja

    Hi !

    It's weird to use Excel like a database as it is just a calculator
    Any serious database is under Access, not within Excel !
    We are all very ignorant, what happens is that not all ignore the same things … (Albert Einstein)
  8. Chihiro

    Chihiro Excel Ninja

    Bit of faulty logic here. Different programming and optimized for different operation.

    But at any rate, you need to pick the right tool (within Excel) for the job. Be it PivotTable, MS Query, PowerQuery, PowerPivot, VBA etc etc.

    With proper design and tool, Excel can handle surprisingly large data set with little issue.
  9. Stephan

    Stephan Member

    Rows/Columns is a spreadsheet/database. Would be inefficent to hold data without grid referencing of Rows/Columns, such as NOTEPAD!

    Access isn't any better, it's Data Validation with bespoke VB is also incoherent and ungeneric. And DATA ENTRY via USERFORMS can be easily replicated in XL can selecting cells and choosing from MENU: DATA > FORM.

    Even examples with Office package for Access, apart from looking intially as fully functional, well try to replicate that on your own data & summarising categories concept, gd luck with that! That's intentional, more training, more program upgrades, etc.

    Everybody has an individual perspectutive, as an end user, the lack of intuitive MENU driven data validation is intentional to give reasons persons to get the next version of OFFICE, after 2 decades of blantant devolved DV of what would be easy to replicate from AUTOFILTER comboboxes in a seperate tab displaying DYNAMIC RESULTS > DYNAMIC CHARTS, of course that is simple for Program Developer, the persons who code XL.

    It is totally obvious, that if Program Developer offered total solution, then there would be no reason for buyers/persons to UPGRADE to next version of OFFICE.

    Hence why OFFICE365 has been introduced annual registration payment giving you a reason to pay/buy again, surely many persons not naive/gullible has realised the real reason, other then to make initial purchase cheaper.

    But does DV solutions offered in OFFICE 2013/2016 provide following with results that are always coherent, reliable & intuitive?: that's a big question...

  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Hi ,

    You say that if a Program Developer could offer a user a total solution , there would be no need for upgrades to an Office version.

    This may well be true , but there are two problems in this approach :

    1. Every user who wanted advanced features not available in their existing Office version would have to approach a Program Developer , assuming that there are enough Program Developers with the same kind of expertise as the Microsoft Office development team.

    2. The Program Developer would obviously charge the user for his time and expertise and effort in developing a bespoke solution.

    As an example , try getting a Program Developer to develop a bespoke Goal Seek or Solver solution for you.

    Microsoft is able to provide the expertise and time and effort that went into developing upgrades and advanced features only because of the volume that they are able to sell ; not every Program Developer will have a million clients.

    Marc L likes this.
  11. shrivallabha

    shrivallabha Excel Ninja

    Just to add to this one.

    Maintaining in-house solutions is not so easy either. e.g. original developer decides to leave the organization and there's no backup / source code.

    Sometimes updating the software is mandated by the OS upgrade or in some cases e.g. CAD systems in line with the client's requirement. This is not so easy to avoid especially when companies want their systems to be more secure or aligned etc. to save hassles.

    So keeping the rest of environment at the same state (just because you have a development for one application) may not be as easy / feasible as you'd like to think.

    These processes are complex in organization and do not run on whims and caprices of individuals and their tastes. You can do that with your home system.

    Of course this doesn't mean, organizations are always against developments. But developments get through after only careful evaluation of many things including financing and weighing returns/benefits.

Share This Page