Working with Microsoft Excel
If You’ve Never Used Excel
Even if you’ve never used Excel, you’ll find that it’s pretty easy to figure out on your own, especially if you’re already used to using Word. Don’t be afraid to use trial-and-error. If you accidentally make a change you don’t want, immediately hit the ‘undo’ button (the arrow just to the right of the floppy disk-looking ‘save’ button, up in the top left corner of the screen), and everything will revert you back to where it was before.
If you can’t figure something out that way, Excel Help is your friend. Access it by clicking the little circle with the question mark up in the top right corner or the screen. Either put in search terms or simply browse the menus and sub-menus until you find the topic you want.
Google can also be your friend. Sometimes it’s just as fast, if not faster, to do a Google search for how to do something.
- To see how to insert cells, rows, or columns: go to Excel Help and follow this path: Worksheet and Excel table basics: entering or editing data: insert or delete cells, rows, or columns.
- To see how to delete rows or columns: go to Excel Help and follow this path: Worksheet and Excel table basics: entering or editing data: insert or delete cells, rows, or columns.
- To delete cells: DON’T. Deleting cells will mean other cells have to shift into their place, and that can get tricky. Clear cell contents instead.
- To clear cell contents: highlight cells you want emptied (by the cell or in blocks of cells or in whole columns/rows by clicking on the column letter or row number at the edges of the sheet), right click, and choose ‘clear contents.’
- To see how to format cells: go to Excel Help and follow this path: Worksheet and Excel table basics: formatting data. Click on applicable topics. Note: it’s especially helpful to know the ‘number’ tab. Occasionally, inputting a number in a cell will result in Excel trying to process it as a date. (example: if you input 2-4, Excel will convert that to 4-Feb.) If this becomes a problem, change the formatting for that cell to the ‘text’ category. The corner of the cell will show a green triangle, but that’s perfectly fine.
- To change column width: Make sure that column is selected, then hover the mouse near the edge of the column up in the column header (A, B, C, etc.) until it turns into a symbol that looks like a vertical line with arrows pointing out of each side, click and drag to resize.
- To do word wrap in a cell (so that the cell shows its entire contents): Make sure the cell or cells are selected, find the box in the top menu marked ‘Alignment,’ and select the last icon in the top row. (You can also do this through the ‘format cells’ menu, under the ‘Alignment’ tab.)
- To freeze the header row (so that it stays at the top even as you scroll down): If it’s the first row (which it should be), you don’t have to highlight it; just click on the ‘view’ tab at the top of the screen, select the ‘freeze panes’ dropdown menu, and select ‘freeze top row.’ If it’s not the first row, highlight the row(s) you want to freeze, click on the ‘view’ tab at the top of the screen, select the ‘freeze panes’ dropdown menu, and select ‘freeze panes’
Helpful Hints For When You’re Working On Metadata
When importing tab delimited or other .txt files
- Open Excel first, then open the file. Do not open by right-clicking on the file itself.
- The Text Import Wizard is your friend. It has three screens:
- Step 1 of 3: You shouldn't need to to anything here. It defaults to 'Delimited.'
- Step 2 of 3: This allows you to set what the delimiter is. It defaults to 'Tab,' so if you have a space-delimited file (such as a script might spit out), you need to select 'Space.' The preview at the bottom of the screen shows you what the results will look like.
- Step 3 of 3: This lets you set the format for each column. Normally, you don't need to mess with this. Two times you might:
- You want to skip some columns. If so, select the column in the preview screen and then change the Column Data Format to 'Do not import column (skip)'.
- You want to change or set the format for a particular column. If so, select it in the preview screen and make the necessary changes in the Column Data Format.
- How do I keep my import from interpreting certain numbers as times?
- This is usually a problem with the Format column in the metadata.
- Example: 3 p. becomes 3:00 PM
- It's really only fixable during import, not afterward.
- During import, at step 3, select that column and choose 'Text' in the Column Data Format panel.
- This is usually a problem with the Format column in the metadata.
Dealing with repetitive words/phrases
When working on a collection, you might find certain words or phrases pop up a lot. Excel is sometimes smart enough to take care of that problem for you by filling in the rest of the cell if the first letters/words are similar to what it’s seen before. (Watch that: sometimes it fills in things in a way you don’t want it to!)
There are several ways of dealing with repetitive data so you don’t have to type it over and over again.
- Copy the contents of a whole cell to another by copy/paste: copy that cell by clicking on ‘copy’ in the ‘Clipboard’ menu or using control + C, then paste that info into another cell by clicking ‘paste’ in the ‘Clipboard’ menu or using control + V; repeat as needed.
- Copy the contents of a whole cell to another by cell border dragging: when the cell to be copied is selected, click on the little black box on the bottom right corner of the border and drag that border until it encompasses the nearby cells you want it to (typically, down a column). Important note: For text, this will copy the cell contents as is. For numbers, it will increase them by increments. This change by increments is good if you’re inputting consecutive numbers like filenames. But if you want the numbers to repeat as is, you need to have at least two consecutive cells with the same number, then highlight both of them before your drag the border.
- Invent your own system of placeholders: Proceed carefully!
- When typing a long phrase over and over, it’s sometimes helpful to invent an abbreviation for it (typically an acronym; example: ‘Woodward Iron Company’ becomes ‘WIC’ or ‘wic’) which you will later change back into the long phrase with the find/replace tool. Make sure such an acronym is not already a word, so that you won’t be replacing all those instances of that word with your phrase. One way to take care of that is to use all caps, then check the box for ‘match case’ when you get into the find/replace menu.
- Procedure for find/replace:
- From the ‘Home’ tab, look at the ‘Find & Select’ menu and click ‘replace.’
- Input your abbreviation in ‘Find what’ and your longer phrase in ‘Replace with.’
- Select the ‘Find All’ button and review the results to make sure you’re find/replacing what you actually want to.
- Select the ‘Replace All’ button.
- Close the ‘Find and Replace’ screen.
- Note: You can also do this sort of find/replacing of placeholders visually. If, for instance, several cells in a column will be the same (example: a repeated measurement), you can fill them in with some placeholder word so that you could go back and find those cells later and use copy/paste and/or dragging the border to fill them with the correct data. This obviously only works on whole cells. I often use the word ‘same,’ to remind myself that the data should be the same as the previous cell.
Highlighting cells by formatting cell/text color
Sometimes, you’ll find it helpful to highlight a cell, by changing either the text or background color so that it will stand out. You shouldn’t leave cells like this in the longterm, unless you really need to draw attention to an error; but as you’re working, highlighting cells can help you keep your place or remind you of something you need to go back and do. Just don’t forget to reformat the cell to normal before you finish for the day. Start by highlighting the cell(s) you want to format, then do one of the following:
- While on the ‘Home’ tab, look at the ‘styles’ menu (or select its dropdown) and select one of Excel’s preset cell formatting options (for example, the pink ‘Bad’ or the orange ‘Input’). When you want to change it back, follow the same procedure, only select ‘Normal.’
- While on the ‘Home’ tab, look at the ‘font’ menu. Click on the symbol with the paint can to change the fill color of the cell; change back by selecting ‘No Fill.’ Click on the symbol with the big A to change the font color; change back by selecting ‘Automatic.’
- Right click selected cells and the ‘font’ menu will pop up. Follow procedures from # 2.
Adding a comment to a cell
Sometimes, it’s helpful to add a comment to a cell in order to explain its contents, why it is the way it is or what needs to be done with it. Try to use these sparingly on the finished spreadsheet, and only of great necessity. Comments are better used as you’re working, as a reminder to yourself about something. Just make sure you delete comments when you’re done working if they’re just reminders to yourself and not more formal notes to other students working on the collection, or for those who might be finalizing the spreadsheet.
You can add a comment to a cell by right-clicking it and selecting ‘add comment.’ It will pop up a box that you can type in and format as you would any other text (bold, underline, etc.). Click anywhere outside of the text box to save and add it to the cell. If you want to edit or delete the comment, right click the cell and select those commands from the menu.
Sharing a Spreadsheet
- Make sure you are the only one who has the spreadsheet open
- Go to the Review tab and choose Share Workbook
- Select the checkbox for 'Allow changes by more than one user at the same time'
- While still in that popup menu, check the Advanced tab to make sure Update Changes is set to 'When file is saved' and 'Save my changes and see others' changes'; and that Conflicting changes between users is set to 'Ask me'
Unsharing happens in the same manner, except you deselect the checkbox
- See Excel_getting_file_count_from_ContentDM.docx on how to get a file count from a ContentDM export.
As of 2010, this is somewhat irrelevant given our shift to Acumen, still this tutorial has some techniques that can be applied to other situations.