User Profile
Mark_Walters
Copper Contributor
Joined Mar 28, 2023
User Widgets
Recent Discussions
Re: Unable to insert a column without losing data
Hi uyanga If I understood your query correctly, you may still need an answer. Excel has a limit of 16,384 columns, and if you have already filled all those columns with data, you cannot insert any more columns without first deleting some data to make space. These are your options:- A) If the columns at the end of the sheet appear to be empty, first make sure they are: Select the columns you think are empty, then select 'Column differences' from 'Go To Special' (under Find and Select on the Ribbon 'Home' tab). > This should display a message "No cells were found". * It should then be safe to delete those columns to remove formatting that could be blocking the insert. * Alternatively, if you want to play safe, you can use the options under the 'Clear' button (also on 'Home' tab), starting with 'Clear Formats' but skipping 'Clear all' and 'Clear contents' (which would delete any data). > However, if you don't get the above message, then all the cells with data should be highlighted, and the first cell with data will be selected. If the cell(s) look empty, the data may be hidden, then look in the Formula Bar, and not the cell itself. If you find valid data you want to keep, then you are stuck with a workaround. B) If all your columns have data, you have limited options, which all amount to workarounds. B.1) Start a new sheet in the same workbook or start a new workbook to contain the new data. B.2) Similar to B.1, copy (some) data from your current sheet to a new sheet/workbook and delete the copied data from your original sheet so that you can insert again. This might be better if you have processes that depend on your (latest) data remaining in a known place. #Note: The 'copy sheet' feature would be one way to achieve either of the above especially if you want to retain formatting - right click on the sheet tab to display the context menu, select 'Move or Copy' and remember to click on the 'Create a copy' checkbox. Then delete whatever makes sense from the original and copy so that you have a complete set between the two. It may be wise to make a backup copy first. B.3) If you need all the data on the same sheet, it then provided you have enough rows, your only option is to add your new data on a new set of rows. You will no longer be able to insert an entire column, but you can still insert new columns on those rows you are adding data to. C) If none the above help, it may be time to leave Excel behind, at least in terms of data storage, and use something like Access to store the data, and possibly use Excel to just run queries on it.3.6KViews0likes0CommentsRe: Speeding up a slow-running formula on large Excel dataset
PatDools So, the best solution is probably to simplify the formula itself, or change the solution altogether. I can't be certain this will be quicker, as I don't have your data or set-up, but I would be surprised if it doesn't help. Basically I created a helper column in column "J" containing the formula '=E2&G2&H2&I2', and then added a '=SUMIF(J2:J15,E2&"2019"&H2&I2,D2:D15)' formula to get the total (I only had test data in rows 2 - 15). On my simple test set this gives the same result as the SUMPRODUCT solution, unless I misunderstood your scenario. (One can then hide the helper column to keep it looking decent). [A similar helper column option would be as follows '=(E2&G2&H2&I2=E$2&"2019"&H$2&I$2)*D2' - then you can just sum the column, and it won't look so messy if you don't like the idea of hiding it]. The above is assuming you are just looking for a single total to be displayed, if I am wrong and the SUMPRODUCT formula is on every line, then along with the aforementioned helper column, replace it with this version of SUMIF: '=SUMIF(J$2:J$15,E2&"2019"&H2&I2,D$2:D$15)'. Hope this helps. Mark1.8KViews0likes0Comments
Recent Blog Articles
No content to show