Often times, you'll do work in one Excel file, that would be useful to show in another Excel file. You can simply copy and paste the data in, but if the data is dynamic (i.e. changes often), you might want to link between the two files. You can link to a cell in another file just as easily as you can link to a cell in the same workbook, the only difference is that you need … [Read more...]
Naming Ranges
An easy way to quickly reference a cell or list of cells is to define them with a named range. To do this, simply select a single cell or a range of contiguous cells, then click the name box next to the formula bar and type the name you wish to assign to those cells and press Enter. Your name must begin with a letter and can't have any spaces (an underscore "_" is a good … [Read more...]
Drop Down Lists Using Data Validation
Adding a drop down list to your worksheet allows the users of the worksheet to enter information in a neat, consistent format. For example, say your worksheet asks a user to input information that later will get summarized in a PivotTable. If the user doesn't enter the information consistently (i.e. spells out words some of the time, and abbreviates the rest), the … [Read more...]
Create and Name a New Worksheet
Say you have a spreadsheet that keeps track of weekly sales information, which you want displayed in a new sheet for each month. You don't want to copy an "original" worksheet each week, so a macro can help to automate this process. The macro code below will copy the worksheet named "Original", and place it after the last worksheet in your file. It will then prompt you to … [Read more...]
Format a List in Columns to a Single Column
Say you have a large contact list in Excel that is formatted, for example like: Column A - Names, Column B - Addresses, Column C - Cities, Column D - States, Column E - Zip Codes. It's great that everything is formatted in neat columns, but it would be a nightmare if you wanted to use this list to print off envelope address labels. Thankfully, there's a macro can speed up this … [Read more...]