Lock pivot Field Setting Layout

jaeremata

New Member
Joined
Jan 20, 2021
Messages
22
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Everyone, I have question regarding pivot. Is there a way in excel to lock the pivot field setting layout? Because whenever I delete the raw data and save it and open again and try to import new one, the settings changed. If you see on the image, that is the settings that I need but what happen is it goes to Show item labels in outline form (first option)., I have 5 pivot on that sheets and have the same issue.
1611661932149.png
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jdellasala

New Member
Joined
Dec 11, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Being that you said you're deleting the "raw data" and then open it again, I'm guessing you're not using Power Query to import the data. If you're unfamiliar with PQ, Mr. E has plenty of excellent videos on the subject as does the ExcelIsFun channel on YouTube. (I mention ExcelIsFun because those videos all have sample files attached with both starting and completed worksheets which is really useful!).

I'm using Office 365. If you're using an earlier versions you should be able to find your way around. If you're using Excel 2010 or earlier, you'd need to download and install the Power Query add-in. Both YouTube channels should have details.

Assuming you're using a CSV file for your Pivot Table, you would go to the DATA tab, and in the Get & Transform Data use From Text/CSV. After locating the file use the Edit/Transform Data button to verify the data and then from the Home tab select the Close & Load DROP DOWN, and select Close & Load To... then select what you want to do with it.
You can load it directly to a Pivot Table with no intermediate Table needed. If you have a large amount of data, checking the Add this data to the Data Model will speed things up and reduce the file size significantly.

Now, when you get new data, you only need to Refresh the Query and Pivot Table (it may take two Refreshes). Your Pivot Table will maintain the options you've chosen. If you haven't already, you may want to uncheck Autofit column widths on update in the Pivot Table's properties.

I know this sounds like a lot of work. For simple situations Power Query is very easy to use, and it should take maybe a few hours to go through some videos and get going, but believe me it will be well worth the time! I used to have to export an "Excel" spreadsheet from a database with over 350,000 records and at least 20 columns and spend at least 10-20 minutes cleaning it up to make it useable daily. I only learned how to use Power Query two months before I was laid off. I could have saved literally hundreds of hours had I learned it years earlier.
Good luck!
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Tabular form can be set as Default layout in new insider office edition .

see the link
 
Solution

jaeremata

New Member
Joined
Jan 20, 2021
Messages
22
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Tabular form can be set as Default layout in new insider office edition .

see the link
This is what I'm looking for. Thank you so much for the help.
 
  • Like
Reactions: alz

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,998
Members
415,873
Latest member
fuulhouse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top