Random Excel Tip

Wednesday, November 11, 2009 @ 1:58 am

If, for some reason, you’re working with an Excel spreadsheet and you need to copy/paste a formula that refers to different worksheets/rows and want to prevent having to manually go through each cell/worksheet, there’s an easy way.

Press CTRL + ~ to toggle code view, then highlight + copy the cells that you want to modify and paste it directly into Notepad. Do a Replace and replace accordingly (e.g. in the below example, replace “S1” with “S2”). Then highlight everything + copy, then in Excel, paste it into the respective cells (Excel will be able to properly parse what you’re pasting since it’s tab-delimited). Then press CTRL + ~ to toggle code view to view your final results.

As shown below, for one of my assignments I had to create three tables that referred to various worksheets. My method was completing one row (e.g. Scenario 1, Simulation Run 1), then using the above method to modify it for Simulation Run 2 and so forth until the entire table was complete (replacing R1 with R2, for example). Then I used the above method (selecting the entire table this time and replacing S1 with S2, for example) and created the following two Scenario tables.

Excel Formula Example Excel Notepad Replace