News:

Thank you for your patience during the Forum downtime while we upgraded the software. Welcome back and see this thread for some new features and other changes to the forum.

Main Menu

Microsoft Excel

Started by kphoger, July 15, 2020, 03:44:24 PM

Previous topic - Next topic

kphoger

Has anyone noticed this change that seems to have taken place a few years ago?

If you copy a range of cells in Excel, then do any other non-viewing action (such as typing or formatting somewhere), then the data drops out of your clipboard and you cannot paste it–neither elsewhere in Excel nor in another program.
Keep right except to pass.  Yes.  You.
Visit scenic Orleans County, NY!
Male pronouns, please.

Quote from: Philip K. DickIf you can control the meaning of words, you can control the people who must use them.


JayhawkCO

I had not. Normally if I'm copying something, I'm pasting directly thereafter.

abefroman329

Quote from: kphoger on April 14, 2022, 11:01:38 AM
Has anyone noticed this change that seems to have taken place a few years ago?

If you copy a range of cells in Excel, then do any other non-viewing action (such as typing or formatting somewhere), then the data drops out of your clipboard and you cannot paste it–neither elsewhere in Excel nor in another program.
If I copy data in any program and don't immediately paste it somewhere, it disappears from the clipboard, and I don't remember it being any different before.

To answer the original question, I took a data analytics course in the fall of 2020, and I use it extensively for work (and for its intended purpose). There are a lot of things that make it impressive, but my favorite feature is probably the ability to create geographic maps (say, a map of the continental US with each state shaded by how flat it is).

kphoger

Quote from: abefroman329 on April 14, 2022, 01:24:12 PM
If I copy data in any program and don't immediately paste it somewhere, it disappears from the clipboard, and I don't remember it being any different before.

Really?  Not for me.

I just did a test:

1.  Type text ABCD into Word.
2.  Highlight that text and hit Ctrl+C to copy.
3.  Type a different line of text, EFGH.
4.  Highlight ABCD and hit Delete.
5.  Save the file to my desktop.
6.  Close Word.
7.  Open Excel, open a new blank worksheet.
8.  Open a (second) new blank worksheet.
9.  Type text WXYZ in a cell.
10.  Click on a different cell, hit Ctrl+V to paste.

And, do you know what happened?  ABCD (which I had copied in step 2) pasted into that cell just fine.




Now let's say I want to copy a table from Excel and paste it into an e-mail, which is something I do all the time at work.
  1.  Highlight the cells, hit Ctrl+C to copy.
  2.  Open a new blank e-mail.
  3.  Hit Ctrl+V to paste.
That works fine, the table pastes into the e-mail without a hitch.

But what doesn't work is this:
  1.  Highlight the cells, hit Ctrl+C to copy.
  2.  Change the color of a completely different set of cells–maybe even from a completely different file.
  3.  Open a new blank e-mail.
  4.  Hit Ctrl+V to paste.
As soon as I do Step #2 above, the cells I had copied are no longer stored in my clipboard, and pasting into my e-mail does nothing.
Keep right except to pass.  Yes.  You.
Visit scenic Orleans County, NY!
Male pronouns, please.

Quote from: Philip K. DickIf you can control the meaning of words, you can control the people who must use them.

MikeTheActuary

Quote from: kphoger on April 14, 2022, 11:01:38 AM
Has anyone noticed this change that seems to have taken place a few years ago?

If you copy a range of cells in Excel, then do any other non-viewing action (such as typing or formatting somewhere), then the data drops out of your clipboard and you cannot paste it–neither elsewhere in Excel nor in another program.

That's been standard behavior in Excel since at least Office 2013 (which I still use very frequently), and perhaps a few versions before that.

Put another way, I don't remember Excel not behaving that way, and I have spent an embarrassingly large amount of time working in Excel over the past 27 years.

kphoger

Weird.  I distinctly remember it not being the case within the last dozen years.
Keep right except to pass.  Yes.  You.
Visit scenic Orleans County, NY!
Male pronouns, please.

Quote from: Philip K. DickIf you can control the meaning of words, you can control the people who must use them.

kalvado

Microsoft is basically loosing track of what they are doing.  Three levels of copying feature in Excel are CutCopyPaste mode within excel; office clipboard; and system clipboard. Aaand... I think they lost the vision of how things should work, so whatever happens - it is no longer a bug, it is a feature.

Scott5114

Y'all are gonna be mad if you ever hear about Linux's middle-click paste...
uncontrollable freak sardine salad chef

ZLoth

I often use Excel to format the data both prior to importing the data into a database as well as data extracted from a database. In fact, there are web libraries that create Excel and OpenDocument files.

One powerful function is with the import of CSV files. If you had too many mangled imports in Excel by double-clicking on the CSV file, try Data → From Text/CSV. This will pre-load the data and you can transform the data formatting from Excel's guess to, oh say, a value into a text field. Especially when your number is a sixteen digit unique identifier, and you don't want Excel to change it to a exponential number.
I'm an Engineer. That means I solve problems. Not problems like "What is beauty?", because that would fall within the purview of your conundrums of philosophy. I solve practical problems and call them "paychecks".

Scott5114

Oh, yeah, opening CSVs in Excel (or any spreadsheet program) is asking for trouble, thanks to autoformatting. I had to help a friend of mine with his homework for over an hour because we just couldn't figure out how to get it to work in ArcGIS, because ArcGIS needed an X-digit number padded with leading zeroes and Excel kept throwing away the zeroes.

Generally I will only glance at a CSV in a spreadsheet program to get an idea of what the data is supposed to look like, then make any sort of changes in a text editor (or for really substantial/repetitive changes, with a Perl script).
uncontrollable freak sardine salad chef

Dirt Roads

Quote from: Scott5114 on April 16, 2022, 05:44:11 PM
Oh, yeah, opening CSVs in Excel (or any spreadsheet program) is asking for trouble, thanks to autoformatting. I had to help a friend of mine with his homework for over an hour because we just couldn't figure out how to get it to work in ArcGIS, because ArcGIS needed an X-digit number padded with leading zeroes and Excel kept throwing away the zeroes.

If you switch the format from "General" to "Number", and then switch to Custom you can set up the correct number of digits.  The conversion to CSV will format the output the same way as the Custom format.  Thus, a 10-digit number with a bunch leading zeroes is formatted using "0000000000" and can include a decimal place such as "0000000000.00".  Just make sure you don't format using commas as thousands placeholders "0,000,000,000.00" because that will mess up your CSV output.

Over the years, I've used a combination of MS-Excel and MS-Notepad to clean up or reformat a bunch of different database formats (including old-fashioned Hollerith input tables).  It's not been fun, but I've never found a commercially-available product that can handle all the junk that they've thrown at me.  When the data got too messy, I just wrote a simple Visual Basic program to grab some of the data and clean up all of the excessive commas so that Excel could handle the rest of the problem.

On much older versions of Windows, these techniques also worked on ASCII, EBCDIC and hexadecimal codes (but the newer versions of MS-Excel wants to interpret those for you nowadays).  If you are trying to preserve that type of data in Excel, you will need to format the fields as text field where the codes are in quotes (and then remove all of the quotes in a simple text editor).  Geesh, does anybody else miss the days that HEDIT.EXE was a standard feature on a personal computer?

Scott5114

I don't have access to Excel, but I do have vi and emacs, so...
uncontrollable freak sardine salad chef

kphoger

Ick, large numbers are something I deal with every day at work.  Unfortunately, I get some of the data sent to me as an Excel file via e-mail with the numbers already having been converted to exponentials–such that changing them back into text rounds them to the nearest million or whatever.  Which isn't exactly useful, considering that those numbers are serial numbers and the last several digits kind of matter.
Keep right except to pass.  Yes.  You.
Visit scenic Orleans County, NY!
Male pronouns, please.

Quote from: Philip K. DickIf you can control the meaning of words, you can control the people who must use them.

jeffandnicole

Quote from: kalvado on April 15, 2022, 03:40:29 PM
Microsoft is basically loosing track of what they are doing.  Three levels of copying feature in Excel are CutCopyPaste mode within excel; office clipboard; and system clipboard. Aaand... I think they lost the vision of how things should work, so whatever happens - it is no longer a bug, it is a feature.

Personally, the more ways they have to do something, the better.

kalvado

Quote from: kphoger on April 16, 2022, 07:07:50 PM
Ick, large numbers are something I deal with every day at work.  Unfortunately, I get some of the data sent to me as an Excel file via e-mail with the numbers already having been converted to exponentials–such that changing them back into text rounds them to the nearest million or whatever.  Which isn't exactly useful, considering that those numbers are serial numbers and the last several digits kind of matter.
There is a book, "humble pi" - about mathematical and near-math issues all over the place. Excel is a subject of a separate chapter.
Phone numbers distorted is a simple one. Issue I really like, though, is more involved:
QuoteIn 2016 three intrepid researchers in Melbourne analysed eighteen journals which had published genome research between 2005 and 2015 and found a total of 35,175 publicly available Excel files associated with 3,597 different research papers...... the researchers were left with 987 Excel spreadsheets which had gene name errors introduced by Excel. In their sample, they found that 19.6 per cent of gene research crunched in Excel contained errors.
Examples are SEP15 and MARCH5 genes which Excel converts into dates.

Scott5114

You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
uncontrollable freak sardine salad chef

kalvado

Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
It is more than that. Complex Excel spreadsheets are essentially programs made by non-programmers, with no testing, often no planning.
From the same book
QuoteThe European Spreadsheet Risks Interest Group (yes, that is a real organization, one dedicated to examining the moments when spreadsheets go wrong) estimates that over 90 per cent of all spreadsheets contain errors. Around 24 per cent of spreadsheets that use formulas contain a direct maths error in their computations.

jeffandnicole

Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".

That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.

Quote from: kalvado on April 16, 2022, 10:02:09 PM
From the same book
QuoteThe European Spreadsheet Risks Interest Group (yes, that is a real organization, one dedicated to examining the moments when spreadsheets go wrong) estimates that over 90 per cent of all spreadsheets contain errors. Around 24 per cent of spreadsheets that use formulas contain a direct maths error in their computations.


Guilty as charged. Also, some of the formulas I use are more complicated than they need to be because of my knowledge at the time of its creation.

That said, many people who say they "know Excel" can do =A1+A2, or a simple common formula.  I mean, sure, I can get a great grade if a spelling test consists of 3 letter animals words. But the more complicated a test is, the more likely I'll get something wrong. No different with a more intense Excel file. It just takes someone...or a few people...to analyze the results to confirm they calculated properly.

Scott5114

Quote from: jeffandnicole on April 16, 2022, 10:30:39 PM
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".

That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.

Well, I know how to use a hammer. Everyone who I talk to knows how to use a hammer. I don't know how to use an air conditioner manifold gauge or any other manifold gauge. Most others I talk to don't know how to use an air conditioner manifold gauge or any other manifold gauge.

But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.
uncontrollable freak sardine salad chef

Rothman

Now tell us how many Access databases have bad data or bad joins.
Please note: All comments here represent my own personal opinion and do not reflect the official position(s) of NYSDOT.

Scott5114

#70
I'm not suggesting that Access is the solution, because it's not. That's the problem–there is no good, easy database application for your day-to-day office worker. Access is the closest to it, but it has a lot of failings, and the open-source competitors have even more because open-source programmers are content with writing SQL and see no real need to provide a graphical data-management tool.

DB Browser for SQLite is about the best solution that I've found, and it works well for my purposes, but I wouldn't feel comfortable giving it to Lynette from Accounting or anything. Something like phpMyAdmin could work, but it requires a server, and managing user-level access control is way too much for most people to want to mess with for the applications that tend to fall into the trap of spreadsheet-as-a-database.

Maybe I should write something.
uncontrollable freak sardine salad chef

jeffandnicole

Quote from: Scott5114 on April 16, 2022, 11:30:44 PM
Quote from: jeffandnicole on April 16, 2022, 10:30:39 PM
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".

That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.

Well, I know how to use a hammer. Everyone who I talk to knows how to use a hammer. I don't know how to use an air conditioner manifold gauge or any other manifold gauge. Most others I talk to don't know how to use an air conditioner manifold gauge or any other manifold gauge.

But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.

This isn't relevant because I'm sending what I created to a specific group of people who know how to use what I'm sending them (How do I know that? Because we converse about the data contained within.) If a new person is involved, I inform them of the features of the spreadsheets, as certain things such as hyperlinks aren't really visible, and I've hid the tabs at the bottom as the names aren't related to the data within each sheet.

Also, how many conversations do you really have with people about hammers and air conditioning manifold gauges?

Scott5114

#72
Quote from: jeffandnicole on April 17, 2022, 12:16:22 AM
Quote from: Scott5114 on April 16, 2022, 11:30:44 PM
Quote from: jeffandnicole on April 16, 2022, 10:30:39 PM
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".

That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.

Well, I know how to use a hammer. Everyone who I talk to knows how to use a hammer. I don't know how to use an air conditioner manifold gauge or any other manifold gauge. Most others I talk to don't know how to use an air conditioner manifold gauge or any other manifold gauge.

But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.

This isn't relevant because I'm sending what I created to a specific group of people who know how to use what I'm sending them (How do I know that? Because we converse about the data contained within.) If a new person is involved, I inform them of the features of the spreadsheets, as certain things such as hyperlinks aren't really visible, and I've hid the tabs at the bottom as the names aren't related to the data within each sheet.

Also, how many conversations do you really have with people about hammers and air conditioning manifold gauges?

Your training department failed you, then. None of that is relevant to the difference between a spreadsheet and a database.

Also, since I'm working on a construction project and friends with an HVAC tech, a lot?
uncontrollable freak sardine salad chef

jeffandnicole

Quote from: Scott5114 on April 17, 2022, 12:41:06 AM
Quote from: jeffandnicole on April 17, 2022, 12:16:22 AM
Quote from: Scott5114 on April 16, 2022, 11:30:44 PM
Quote from: jeffandnicole on April 16, 2022, 10:30:39 PM
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".

That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.

Well, I know how to use a hammer. Everyone who I talk to knows how to use a hammer. I don't know how to use an air conditioner manifold gauge or any other manifold gauge. Most others I talk to don't know how to use an air conditioner manifold gauge or any other manifold gauge.

But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.

This isn't relevant because I'm sending what I created to a specific group of people who know how to use what I'm sending them (How do I know that? Because we converse about the data contained within.) If a new person is involved, I inform them of the features of the spreadsheets, as certain things such as hyperlinks aren't really visible, and I've hid the tabs at the bottom as the names aren't related to the data within each sheet.

Also, how many conversations do you really have with people about hammers and air conditioning manifold gauges?

Your training department failed you, then.

Also, since I'm working on a construction project and friends with an HVAC tech, a lot?

My training department is supposed to teach me every formula, option and nuance of Excel?

Scott5114

You're not getting what I'm saying. I'm saying, there are certain applications which shouldn't be attempted inside Excel to begin with because it's not the right tool for the job.

If you need to basically automate calculation of the same formulas over and over again–like, say, you're taking the length and width of a thing to calculate its area, then taking that area to calculate its weight–a spreadsheet is the right tool for the job.

If what you're really wanting to do is store a number of items, all of which have the same properties–like, say, making a list of customer names, addresses, contact information, and such–and you do that in Excel, you are creating an unholy mess. A database is the right tool for that job. If you use a spreadsheet, it is not a matter of if but when that decision is going to bite you in the ass. Worse yet, most people don't even have the training to realize the source of that sharp pain in their ass. That is the problem.
uncontrollable freak sardine salad chef



Opinions expressed here on belong solely to the poster and do not represent or reflect the opinions or beliefs of AARoads, its creators and/or associates.