[Excel For Mac 2016 Not Saving The Settings
Kody Coste
Vicki has made quite a few changes to the configuration options in Excel, visible in the various tabs of the Excel Options dialog box. She wonders if there is a way to save these settings, as she's modified them, so that they can be loaded into another system or backed up.
There is no single place that Excel configuration settings are stored, so the answer to this question is not as simple as one might hope. Some of the settings are stored in the XLB file maintained by the system. Where this file is stored depends on the version of Excel you are using and the way your system is set up. Here, for instance, is where it may be stored on a system:
Other configuration options are stored with individual workbooks, and still others are modified on-the-fly based on the first workbook you open within Excel. Backing up workbooks is easy, but you'll need to do some figuring out to see which ones you want to save based solely on the configuration settings they may affect.
Of course, you may be able to locate third-party add-ons for Excel that you can use to gather and save all of the configuration settings; a bit of time spent with your favorite search engine may turn up some good candidates.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
When saving/opening CSV files in Excel, the default separator is a semicolon ;, as I need it to be a comma , I tried to change it by changing the Language and Regional settings following several other posts about this issue (i.e. How to get Excel to interpret the comma as a default delimiter in CSV files?).
However, after changing the List separator in those settings, my Excel keeps saving CSV files with a semicolon. Is it because Excel was installed while my list separator was set as a semicolon? or is there another setting I am missing here?
I found the problem. My decimal symbol in the Regional settings was also a comma (European) so, even when my List separator was a comma, the CSV was saved with semicolons. Now I changed my decimal symbol to a point and now the CSV file is created correctly with commas as separators. I tested this twice and now know that there must be an exception: if the decimal symbol is a comma, then the list separator will be a semicolon even is set otherwise.
For some reason, every automation I do using excel files is not saving the changes in the files after the execution is done. The rest of the automation works just fine, I can see each step working as they should, but when I open the file it is like nothing has been done.
Does this occur in Studio or Assistant? If latter and try to modify xlsx file via relative path, file which exists under c:\users\[username]\.nuget\packages\[packagename]\[version]\lib\net461 (or net6.0-windows7.0) will modified.
Can you try to check the above path and/or use absolute path?
From this screenshot it shows you are telling the bot what to write but have not told it where to write. If you look at the red alerts you can see that there are errors. In the vlookup you have not defined the range.
Imagine this: you want to export your Excel data to another application, so you go save it in the CSV format supported by many programs. Whatever CSV option you use, the result is a semicolon-delimited file instead of comma-separated you really wanted. The setting is default, and you have no idea how to change it. Don't give up! No matter how deep the setting is hidden, we'll show you a way to locate it and tweak for your needs.
To get a CSV file with another field delimiter, apply one of the approaches described below.
Change separator when saving Excel file as CSVWhen your save a workbook as a .csv file, Excel separates values with your default List separator. To force it to use a different delimiter, proceed with the following steps:
Note. Obviously, the changes you've made in Excel Options are limited to Excel. Other applications will keep using the default List separator defined in your Windows Regional settings.Change delimiter when importing CSV to ExcelThere are a few different ways to import CSV file into Excel. The way of changing the delimiter depends on the importing method you opted for.
For Excel to be able to read a CSV file with a field separator used in a given CSV file, you can specify the separator directly in that file. For this, open your file in any text editor, say Notepad, and type the below string before any other data:
For example, to correctly open a semicolon delimited CSV in Excel, we explicitly indicate that the field separator is a semicolon:
Choose delimiter in Text Import WizardAnother way to handle a csv file with a delimiter different from the default one is to import the file rather than open. In Excel 2013 an earlier, that was quite easy to do with the Text Import Wizard residing on the Data tab, in the Get External Data group. Beginning with Excel 2016, the wizard is removed from the ribbon as a legacy feature. However, you can still make use of it:
In step 2 of the wizard, you are suggested to choose from the predefined delimiters (tab, comma, semicolon, or space) or specify your custom one:
Specify delimiter when creating a Power Query connectionMicrosoft Excel 2016 and higher provides one more easy way to import a csv file - by connecting to it with the help of Power Query. When creating a Power Query connection, you can choose the delimiter in the Preview dialog window:
Change default CSV separator globallyTo change the default List separator not only for Excel but for all programs installed on your computer, here's what you need to do:
How does all this relate to the CSV delimiter? The point is that the List separator (CSV delimiter) and Decimal symbol should be two different characters. That means setting the List separator to comma will require changing the default Decimal symbol (if it's set to comma). As the result, numbers will be displayed in a different way in all your applications.
Moreover, List separator is used for separating arguments in Excel formulas. Once you change it, say from comma to semicolon, the separators in all your formulas will also change to semicolons.
If you are not ready for such large-scale modifications, then change a separator only for a specific CSV file as described in the first part of this tutorial.
That's how you can open or save CSV files with different delimiters in Excel. Thank you for reading and see you next week!
Amazing! Thank you for your clear explanation. I am using excel 2013 and having a hard time with comma separated CSVs, excel always expects a separation by semicolon. Using your tutorial I was able to change in Region settings the default list separator to be comma (,) instead of semi colon (;). It worked perfectly, excel now splits the comma separated values as it should. Thanks!
File is .csv (perhaps because it's been renamed from a .txt file) but Excel opens it with all columns in each row in a single column (i.e. not recognizing separators). In this example, we want comma separators.:
This is the most recent post on that topic I could find, which tells me, that this problem has still not been SUSTAINABLY resolved by the "software giant" (as one said here) Microsoft !!!
The only sustainable way to resolve it - and not the whole world is the United States of America, so we DO NEED such a resolution - would be a seamlessly added EXPORT filter, allowing to replace the the decimal and field separators of the csv saved by Excel !!!
Actually this could also be achieved - as a workaround - by a well-crafted VBA macro.
But we DO NEED this as a built-in function of Excel and this can only be done by Microsoft as the manufacturer of their (international ???) Office suite, right ?
Remark:
When writing such a macro (as a workaround), you would not only have to replace , and ;
Be aware that the csv specification also allows the use of of the separator WITHIN a field, when using special delimiters, like that:
..... , "This is a text, aint it", 64.7, ...
So you would have to take care of such "exceptions", otherwise you'd spoil the whole csv.
Anyway thanks to all, contributing ideas to this thread, including Svetlana for describing all EXISTING workarounds (which are only a crutch in my opinion; sorry Svetlana, that's not your fault of course !)
FINALLY...
Would anyone of you know where to request such a "feature" at Microsoft directly ?
Any developers forum we could jointly add such a request and could gather others, who hopefully agree with us and demand such a priority change with their next release ?
If you use English locale and you in Excel change decimal separator from system to comma, Excel understand that he cannot use comma as a field delimiter and will use semicolon instead of it and will save csv file in non-english format, it is for example: 1,5;23,45.
But, in the case you are working with for example Czech locale (or probably with French locale) and you in Excel change a decimal separator to dot as correct for English csv and save this file. The format of file is for example: 1.5;23;45 and not expected 1.5,23.45. The reason is, I guess, that Excel has no any reason to change a field separator from semicolon to comma, because semicolon is a correct field separator for csv file and and does not contradict with the dot or comma decimal delimiter at all.
795a8134c1