3 ms·
Like insert cells, shift existing cells right or down in Excel?
by danbruc 10y ago
Like insert cells, shift existing cells right or down in Excel?
- digi_owl 10y agoyep. Thing is that this is likely to break existing formulas etc at the best of times. Its funny how it seems only Apple has a spreadsheet the offers free floating "sheets", but there it seems to be treated more like a presentation/layout refinement (meaning that even the "humble" spreadsheet is basically a tool for desktop publishing). I do believe Excel and others have the ability to reference cells from other sheets, but that still means having to flip between them to keep things in sync.
- danbruc 10y agoFormulas will only break if you got them wrong, usually if you confused relative and absolute addressing. Or if you perform your own cell address calculations and the cells are no longer where your formula says. I am pretty sure it is almost always not the fault of Excel.
- rahimnathwani 10y agoYup. A few rules I try to follow when designing spreadsheets that will be used for more than 15 minutes: - Any cell that contains a numerical value (as opposed to a formula) is highlighted. All such cells are at the top of the first sheet. - Never ever use VLOOKUP or HLOOKUP, as inserting columns or rows will break them. Use INDEX and MATCH instead. - Never use OFFSET, as it's a 'volatile' function, meaning Excel will recalculate the value for any cell which contains it, if any value in the sheet changes. Use INDEX instead. - When you're tempted to enter a value directly in a formula (e.g. when you divide by 12 for the number of months in a year), just don't do it. Move the value to an input cell. This goes also for parameters in an OFFSET formula.
- chancancode 10y agoI did some of that in part 2! For the most part, it is smart enough to do the right thing for simple operations like cut and inserting rows/columns, however they are indeed some edge cases that it doesn't handle well.