1,607 votes539 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →
Thanks very much for your votes. And to those who took the time to fill in the survey, thank you! This is a brief update to let y’all know that we’ve started work on this feature request.
- Urmi [Msft]
Why does that not surprise me!!! If MS wants to support CSV, they really ought to do it right. Thanks for trying.
Double Quotes in - Format as Text
Formatted as Text - Double Quotes out
Should be simple. Lets get it done.
If only Excel would save Text Formatted fields with double quotes around them in the CSV file (a csv standard), then the problem would be solved.
If only... sigh...
950 votes96 comments · Excel for Windows (Desktop Application) » Charting, Mapping and Visualizations · Flag idea as inappropriate… · Admin →
Thanks for this suggestion! We are evaluating this for a possible future release. Please continue to vote on this idea.
1,326 votes71 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →
Thanks very much for your votes. And to those who took the time to fill in the survey, thank you! This is a brief update to let y’all know that we’ve started work on this feature request along with the one on changing numbers to scientific notation.
- Urmi [Msft]
2,165 votes510 comments · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →
Thanks everyone for all of the passion about this suggestion! The number of votes has increased greatly in the last couple months and we’re taking notice! We’ve got a bunch of other Excel endpoints behaving this way already and we’re evaluating getting it done in the Windows versions sooner based on the number of votes it gets – so keep the votes coming!
Eric Patterson (Program Manager – MSFT)
165 votes26 comments · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →
10 votes2 comments · Excel for Windows (Desktop Application) » Macros and Add-ins · Flag idea as inappropriate… · Admin →
Unfortunately MS is no longer actively developing VBA. It is as far along as it will ever get. The Excel libraries will probably be updated as new features are added to Excel, but I don't expect to see any further enhancements to the VBA IDE itself. Fortunately, MS has no plans to discontinue VBA either. There are too many millions of lines of customer VBA code written for MS to just kill it. Killing VBA would probably be the death of Excel. Here's a short little writeup written by a MS Excel MVP that explains the future of VBA.
On a positive note, I have also read that MS is planning on replacing VBA with something a little more robust. Perhaps, and this is just a wish list item for me, MS will integrate VBA like development into Visual Studio, which is capable of supporting many different languages, including 3rd party languages. Just imagine the possibilities if you could pick the language you wanted to write your macro in.
I'm keeping my fingers crossed for a more robust environment than VBA provides.
694 votes195 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
Thanks to Graham for starting this conversation. If you would also like Excel to maintain named range references and structured table references in the “applies to” field for Conditional Formatting rules, please add your comments and vote this one up. We will prioritize accordingly.
Steve (MS Excel)
I'm not trying to make excuses for MS here, but maybe they don't know how we want excel to work.
For example, when we copy a cell that is formatted using named ranges to another cell in a different column, in the same table, how should the formatting be copied? Should it make a new rule? Or should it abandon the source formatting and assume the formatting of the new destinations column. I'm for the later.
What about if you have a format spanning 2 or more columns and I want to insert a column in the middle, should it be excluded from the format, or should it assume the formatting of the columns it was inserted between. And what about moving a column with unique formatting between the two spanned columns? Once again, how should that be handled?
Let's give MS some ideas how to fix this existing disaster instead of just complaining about their lack of action.
I agree with you 100%. I think the difference with my previuos note and what you are describing is that I was applying the conditional formatting to columns in Formatted Tables; ie "Applies To:=Table1[Column1]". I'd guess that you weren't using Formatted Tables.
To me, a Formatted Table is any table where "Format as Table" was applied.
For a formatted table, the normal way of adding a new row by simply starting to type in a new row at the end of the table. All call formats and formulas will automatically be applied to the new row. Conditional Formatting will also get applied to the newly added row. Looking at the Cond Format rules will show that the "Applies to" range will be extended to include the new row.
But as you said, "copying" a cell to a new row will still make a mess. But when it did, the new rule it added was unnecessary.
Table1 = is C2:D11
Headers for Table1 are in Row 1
Col C ($C$2:$C$11) formatted for cell = 1
Col D ($D$2:$D$11) formatted for cell = 2
Copying C5 to C12 creates a new new row in the table. So the Cond Format for Col C (and Col D) changed to to $C$2:$C$12 (and $D$2:$D$12) in order to accomodate the new table row. But it also added a separate rule just for $C$12. The rule for $C$12 is not needed because the table range was extended. This left 3 format rules; 1) $C$21 2)$C$2:$C$12 3) $D$2:$D$12 . #1 is not needed because it overlaps with #2
I hope this makes sense.
I think whats happening is that because you're doing a copy / paste (not paste special values), it insists on copying the Cond Format too even though the format is exactly the same as table column format.
Bottom line is that this still makes a mess. MS needs to allow us to put "Applies To:=Table1[Column1]" w/o out converting it to a fixed range. And if the Cond Format matches the table columns format, then drop the Cond Format.
By the way, I do agree with you about paste-special-values. Thats just about the only way I ever paste data. It keeps the formatting rules neat. Unfortunately, a lot of the sheets I create get shared with others that don't understand the intricacies of conditional formats and how copy / paste can mess them up.
And I too am on O365 V 1804 Monthly Channel
@Sbasu, you are correct. MS does seem to have fixed some of it. It appears to be adjusting the "Applies To: range when you add / delete / move whole rows and columns. Or even if you copy/paste within a single "Applies To: range.
But alas, the problem still exists when you copy/paste data that crosses more than one "Applies To" range.
Create a table with a conditional format in Column A and different conditional format in column B. If you copy a single cell from a row in column A and paste into a different row but also in column A, it will work as desired without breaking up your rules.
However, if you copy row data from columns A:B and paste that in a different row, then your rules will get broken, even though the data always stays in the same columns. Same thing would happen if column B did not have any conditional formatting.
You could MOVE the whole row (shift drag row) without breaking the rule. But sometimes you only want to copy a few cells from a row, not move the whole row.
So in summary, your rules will get broken/split-up when you copy/paste and the data you are copying spans more than one conditional rule or non-conditional formatted range.
Clear as mud, right?
PS, I haven't tried the =OFFSET trick from @JOUKE
This is a major pain in the back end. Come on MS, it's time to get this fixed.
1,007 votes848 comments · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →
Thanks for all of the votes – the team has definitely taken notice of the activity around this issue. We moved to SDI as a result of customer requests, but it looks like we’ve got work to do to really nail the use cases people care about. From a read over the comments, I see a number of cases that we will want to investigate further as we think through MDI vs SDI. We’ll get someone from the team to take a deeper look, and we may reach out to some of you for more clarification as we go. Thanks again for all the voting and passion here!
I didn't mean to hijack this thread to be a VBA discussion, so this will be my last post in this thread about VBA. But I did want to clarify a little.
VBA is simply the programming environment and it's associated language. VBA is the look and feel of the VBA IDE as well as the core language components of VB (IF-then-else, Do While, Case, etc.).
But VBA is NOT "Activecell.select" or "Selection.Cut". Those belong to Excel Libraries which are automatically added into your VBA project. This is what allows VBA to talk to Excel.
MS never said it will discontinue development of these Excel Libraries. In fact these libraries are probably exactly the same libraries that Excel itself uses to do virtually everything. If MS stops developing these libraries, then all changes (good, bad, or indifferent) to Excel would also stop.
So what I'm trying to say is that the VBA IDE and core VB language may no longer change, but the functionality within a VBA macro will continue to change and grow as Excel (and its libraries) change. This should include new MDI functionality (if we ever get that back) because Excel itself will be using the same libraries as VBA does.
Roy, I know what you're saying about using VBA to connect to other office apps. It's a major pain in the back end. But I've done it multiple time. I've used Excel VBA to pull data from Access databases based on user entry. I've even done that to MS SQL Server databases. I have also created macros that save the current worksheet (tab) to it's own separate workbook and then attached that workbook to an Outlook email and automatically sent it. All invisible to the user. And I've seen many other examples of people doing similar things.
So while it might be a PITA to accomplish inter-application functionality, I wouldn't rule that out as something thats never being done.
There, no move VBA discussion for me, at least not in this thread.
IMHO the real reason MS killed MDI, is for security / stability reasons (actual stability improvement is debatable). Anyway, one bad acting spreadsheet or VBA module gone awry was able to lock up or bring down all spreadsheets running under a single Excel MDI instance. I believe their intent was to prevent one spreadsheet from crashing them all and losing their associated changes. But in todays day, improved containerization of code and data should easily allow MS to fix the original problem, thus allowing them to bring back MDI. This is basically the same way bad acting web pages no longer bring down the whole browser, but rather just the bad page.
Mats Samuelsson, regarding Visual Studio, it is not a replacement for VBA, nor is VBA a replacement for Visual Studio. They are completely distinct entities with very little overlap except for the "Visual Basic" part of the name. One cannot create a simple VBA macro using Visual Studio, nor can one write code for an Arduino using VBA.
For those interested in the history, VBA is based on ancient Visual Basic 6. VB6 was discontinued 20 years ago. Visual Studio continued to advance VB.net into a very modern language today, but it's almost completely incompatible with old VB6 code. And with VBA being a derivation of VB6, there is almost no crossover available. Fortunately MS has no intention of dropping VBA because of the countless lines of code written in it.
Richard Laycock and Anonymous, there is no reason to accuse Daniel of not knowing how to use Excel. He has his preference just as you have yours. He simply doesn't want SDI to go away. I'm certain he'd be OK with being given a choice between SDI and MDI, just as you are.
Thanks Roy. I'm still stuck back at Version 1902 (Build 11328.20158). I'll need to wait for corporate to push the change to my PC. So it must be new functionality in 1903.
Roy, Thanks for the info. What Version / Build do you have. I don't seem to have this change yet. Thanks.
5 votes0 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
3 votes0 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
3 votes0 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
1 vote1 comment · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
You could probably create a VBA Macro to prompt for the new data. The macro could then find where it needs to paste the data and do that. There is no reason the user would need to unlock the spreadsheet to paste the data. It stays (optionally Password) protected.
In simplified form:
myValue = InputBox("Give me some input")
ActiveSheet.Protect UserInterfaceOnly:=True, Password:="Secret"
ActiveCell.Value = myValue
The Password part is optional and not needed. But it will set the password to "Secret" if the sheet is not currently protected.
4 votes1 comment · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
Under the Date Category (in number formatting) , there is already a mm/dd/yyyy hh:mm PM format which might work for you instead of creating a custom format. Same in the Time category.
52 votes11 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
490 votes76 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
Ditto on Lubomír Tosek's comment. Lets see Left Across Selection and Right Across Selection too. And add vertical across selections too.
47 votes14 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
Importing CSV's has always been a very weak point for Excel. It never get's it right. But I do understand why excel strips the leading zeros, because that's the normal convention for displaying numbers. But I too have been burned by Excel ALWAYS converting the data to numbers (if it looks like a number). A good start would be if Excel simply honored a Double-Quote as text and always text, even if the data looked like a number.
I've also been burned by Excel converting my data to Scientific notation. ie. importing "20E3" converts to Scientific Notation ==> 2.00E+03 . Come on MS, what's the deal with that. Do you really think I intended it to be Scientic Notation. Expecially if it's in a column with other text data like "20K3".
Best solution would be to ALWAYS popup an import dialog (when opening a CSV) and allow us to select the column format we want. And being that it starts as a CSV, break it down to it's constituent columns for us, not the normal column based import dialog. And while you're at it, let us apply/create a number display format for a column while we're importing it.
I'd also recommend the same dialog for exporting CSV's, so we can specify Double Quotes on a number column if desired.
32 votes4 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →