Fix Gene Names Turned into Dates Problem

I became aware of this issue in http://www.sciencemag.org/news/sifter/one-five-genetics-papers-contains-errors-thanks-microsoft-excel?utm_source=newsfromscience&utm_medium=facebook-text&utm_campaign=excel-6929. The following user-defined Function is a start. You can add to the Case statement to add whatever gene names you like, with the caveat that once Excel finds a match, it will jump out of the Case statement. Add this to your Personal Macro Workbook and save it. You can then find the function in the User Defined Functions category of the Function Wizard. Add the function to a separate column, reference the first cell with the problem, copy it down the column, then Copy | Paste | Values into the same column to … Continue reading

Hiding Data on an Excel Spreadsheet

Suppose you need to hide a small section of data in Excel, but you still want to calculate with it. Highlight the cells, right-click, and select Format Cells. Click the Number tab, and select the Custom category on the left. Type three semicolons (;;;) in the Type: field, and click OK. The numbers are invisible, but you can still use them. … Continue reading

Save an Excel Chart as a Template

To save a chart as a template: Create your chart, and then right click it. Select Save as Template… You’ll save a file with a CRTX extension in your default Microsoft Excel Templates folder. To create a new chart based on the template: Select the data you want to chart. Click the Insert tab / Recommended Charts, the All Charts tab, and the Templates folder. In the My Templates box, pick the one to apply, then click OK. Some elements, like the actual text in the legends and titles, won’t translate unless they’re part of the data selected. However, all … Continue reading

Using Range.Offset in Excel VBA

To select a cell in Excel, you have two basic methods: RANGE and CELLS: Range (“A1”).Select Range(“RangeName”).Select Cells(3, 4).Select ‘Selects Row 3, Column 4, i.e. cell D3 Range works well for hard-coded cells. Cells works best with calculated cells, especially when you couple it  with a loop: For i = 1 to 10      Cells(i, 1).value = i ‘ fill A1 through A10 with the value of i Next i Note that your focus does not change. Whatever cell you were in when you entered the loop is where you are when you leave the loop. This is way faster … Continue reading

Mail Merge Problem: Leading Zeroes Missing from Zip Codes

When you use and Excel spreadsheet as a data file in a Word mail merge, formatting zip codes can sometimes make you want to tear all the hair out of your head. This is a particularly annoying problem because most users assume they are solving the problem by correctly formatting zip codes in Excel.  However, correctly formatted zip codes in Excel sometimes still arrive in Word without their leading zeroes. Here is one way to fix the problem permanently: Start Word, and then open a new blank document. Go to Word Options In Word 2007, click the Office Button, and … Continue reading

Deleting vs. Clearing in Excel

Deleting the contents of a cell (by pressing the Delete key) does not delete formatting (although the cell appears to be blank). When you press the delete key, Excel removes the contents and remembers the formatting. To clear both, select Clear All in the Editing group on the Home tab. Better yet, right-click on the Clear All button and add it to the Quick Access Toolbar: … Continue reading

Learn to Use the Format Painter!

Copy Formatting Once To copy the formatting of a block of text and “paint” another block of text with it: Highlight the text whose formatting you want to copy. Click the Format Painter icon once. Your cursor will display a paintbrush. Highlight the text you want to “paint.” Word will format the text. The paintbrush icon will disappear from your cursor. Copy Formatting to Several Blocks of Text If you want to “paint” several blocks of text with a format, you can “stick” the Format Painter to your cursor by double-clicking it. Highlight the text whose formatting you want to … Continue reading