How do i reduce large data in excel?
There are many causes that can help an Excel file to become huge, sometimes Excel spreadsheet becomes so huge while it actually containing very little information. A large-sized Excel workbook may generate lots of issues while dealing with the file, such as file will respond very slow, take more time to save any changes, take time to upload, and more. What’s Making Your Excel File Size Too Big?The main question is “What part of an Excel file causes the big file size. If you don’t know what’s making your Excel file size too big then keep reading the following facts and you will surely get the answer to your query. Huge information: Generally a worksheet having more than 100,000 rows and many columns demands a large memory to store. Pivot Tables: Yes, the Pivot table may also affect the file size. Pivot Tables store the original data hence in each of the Pivot Table have its own data set. Add-ons and plugins: Some plug-ins significantly drop down your Excel workbook performance. If you are running or installing several plug-ins in your Excel workbook then stop doing that. Formatting: In case the sheets are formatted though cell ranges aren’t in use then the file size might increase. Hyperlinks: Sometimes excessive external sources links cause frequent crashing of your Excel application.so find out whether your Excel file is getting crashed or other files linked to it causing such a problem. Media files: If your Excel file containing images/pictures/logo then it can turn your small excel file into a big one. Compatibility issues: Some attributes of Excel won’t work well on all Excel versions. The chances are high that your excel file is undergoing some compatibility issues. So you need to fix it first. Sometimes Excel file’s Meta-data and other hidden contents may also boost the file size. So, there are several causes of a big MS Excel file size hence we have to apply different tricks to reduce it. The solution may depend on the contents of the Excel workbook if you are not sure that which one is affecting your excel file then you should apply them one by one. Let’s start applying the below-given tricks one by one to convert a large Excel file into a compatible size by reducing Excel file size. Method 1# Remove Cell FormattingWhile creating an Excel file we apply several formatting over the spreadsheet, hence deleting formatting will help you to reduce size of Excel file and boost the spreadsheet performance. Steps to clear the Excel formatting Step 1: First, choose the range over which you want to clear the formatting. Step 2: Then go through the: Home Tab > Editing Group > Clear > Clear Formats If the excel spreadsheet containing lots of text then it is the best option. Method 2# Reduce The Size Of The Image File Added In Microsoft ExcelSteps to compress or change the resolution of the image. Compressing doesn’t affect the quality of your excel file. If you don’t want every single pixel in a picture then you can go with the compress option and make the file size smaller.
Note: If you are unable to see the Picture Tools – Format tab then confirms that you have selected a picture. Double-click on the image to choose it and open the Format tab.
Method 3# Convert File type to Binary Format (XLSB)By converting an excel file to the binary format can reduce size of Excel file with ease. So, follow the given steps and save an Excel file in the XLSB format to decrease the file size. 1: Click on the File tab and then go to the Save As option. 2: Now, click on the Browse button to change the file type. 3: From the Save As dialog box, you need to pick the Excel Binary Workbook (.xlsb) file type. Click Save. Method 4# Remove Unnecessary FormulasHaving lots of complicated formulas can affect the file size of an Excel file, so in this method, we will try to remove unwanted formulas from an excel workbook. Sometimes when you have a data set and don’t need the formulas in it then it is better to replace these formulas into values. Follow the below-given steps to do this:
So, all the cells with formulas now get converted into the values. Method 5# Optimize The Used FormulasAfter removing the unwanted formulas, now we will optimize the used formulas by using the following steps: Skip using a volatile or unstable function such as – You need to replace the VLOOKUP, HLOOKUP and LOOKUP function with INDEX & MATCH You
should also replace the nested IF functions with any of the below given: Method 6# Compress The File (ZIP IT)This method is a little different, using the zip file option you can compress the excel file with ease. By compressing an Excel file, you can reduce file size of about 10-15%. Steps to Zip an Excel file: Step 1: First you have to right-click on the Excel File that you want to zip Step 2: Now, take your mouse cursor to the Send to option and click on the ‘Compressed (zipped) folder’ option. That’s it, after creating a zipped file share it with others and the receiver will unzip the file to access the Excel file. Method 7# Delete The Unused CellsIn order to delete the visible unused table, columns and formatted cells follow the below-given step.
Method 8# Reduce Condition Formatting From Excel File
Method 9# How To Reduce Excel File Size Without OpeningIn this method, you will get to know how to reduce Excel file size without opening. This method is different from the zip compressing, This trick is not present on Microsoft Excel but this option is present on every Windows system to compact the size of various files that include Microsoft Excel.
Method 10# Reduce The Size Of Pivot TablesAs we have already discussed that the Pivot Tables consume a lot of memory and may increase the file size. So, we will try to reduce the size of the pivot tables. There are mainly 3 options to reduce the Size of Pivot Tables: Option 1: Sometimes, Pivot Tables are created to check data or seeing an overview. If you don’t need these Pivot Tables in your workbook, then remove the unused pivot table. Option 2: Instead of creating several Pivot Tables having the same source code you should copy and use them where you need. Through this, you can share the same data cache. Option 3: Avoid saving the source data of the Pivot Table with the file. To do this, right-click on the Pivot Table and then go to the Pivot Table Options. Now, from the data tab unmark the tick from ‘Save source data with file’. Method 11# Check And Delete Unnecessary Hidden CellsHidden cells may also add some extra burden to your Excel file. Unwanted hidden cells can boost the size of the Excel file. Microsoft cells get hidden accidentally while working on the rows or columns of the Excel worksheet. If you find out that your file contains unwanted hidden elements then you need to unhide the rows and columns of your Excel worksheet and analyze you need them or not. Delete unwanted hidden cells to reduce Excel file size. What Is The Maximum Excel File Size?If you never want to face this oversized excel file issue again in the future then it’s important to have the right knowledge of maximum Excel file size limit. To know this, you must read this informative post specifically written on Excel specifications and limits. At last, I also want to conclude that make a proper backup of your Excel data first before performing any of these methods to reduce Excel file size. So that if in case the, fixes you have applied haven’t worked then at least you have the option to revert back your original data. Conclusion:All the above-given methods are very effective and will help you to reduce size of Excel file. So, it is hoped that after applying all the above discussed 11 methods to reduce Excel file size you can easily troubleshoot Excel “Maximum Memory or File Size Exceeded” error. If you have any queries or suggestions about this article or suffering from any other kind of issue while using the Microsoft Excel file then share your issue in the comment section of this blog. Summary Article Name Top 11 Tested Ways to Reduce Excel File Size without Deleting Data Description Apply the given 11 Tested Ways to Reduce Microsoft Excel File Size without Deleting Data. Given solutions are compatible with all versions of excel file. Author Publisher Name Repair MS Excel Blogs Publisher Logo Priyanka SahuPriyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels. How do I compress a large amount of data in Excel?Ways to Reduce Excel File Size. Remove unnecessary worksheets, data, and formulas. The number of worksheets and the amount of data contained in an Excel file are directly related to the size of the file. ... . Remove formatting. ... . Remove Pivot Cache. ... . Save in binary format (. ... . Compress the file.. How do I reduce the size of an Excel file without deleting data?Method 9# How To Reduce Excel File Size Without Opening. In order to compact your Excel file, you have to find your excel file using Windows Explorer and then right-click on the file.. Choose Properties from the context menu and then select the Advanced Button.. At last, choose the “compress contents to save disk space…”. |