Working with Microsoft Excel

From UA Libraries Digital Services Planning and Documentation
Revision as of 09:23, 28 September 2015 by Jlderidder (talk | contribs) (Advice for Learning New Excel Skills)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Advice for Learning New Excel Skills

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.

Instructions for how to add and merge information in Excel using ASAP Utilities: File:ASAP utilities to add and merge information.pdf

Basic Tasks

  • To insert rows or columns: right click on the column to the right of / below where you want the moved column/row to go, choose 'insert'
  • To delete rows or columns: right click on the header for the column or row and choose 'delete'
  • To move rows or columns: right click on the header for the column/row you want to move, choose 'cut,' right click on the column to the right of / below where you want the moved column/row to go, choose 'insert cut cells'
  • To delete cells: Try to avoid this -- deleting individual cells or small groups of cells will mean other cells have to shift into their place, and that can get tricky. Clear cell contents when possible.
  • 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 format cells: select cells you want to format, right click and choose 'format cells'; for more information, 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 / row height: Make sure that column/row is selected, then hover the mouse near the edge of the header (A, B, C, etc.; or 1, 2, 3, etc.) until it turns into a symbol that looks like a vertical/horizontal line with arrows, click and drag to resize. Choosing a range of columns/rows will allow you to change all at one time; click the box in the top left corner (beside column headers, at the top of the row numbers) to select the whole sheet.
  • 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’
  • To hide/unhide columns: to hide, select the columns you want to hide and right click and choose 'hide'; to unhide, select the columns bookending the ones you want to unhide and right click and choose 'unhide' (hide/unhide works similarly for rows, although we don't usually have occasion to do that)

Intermediate Tasks

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.

Highlighting duplicates

Note: Don't do this on an enormous spreadsheet, as it will cause Excel to crash. If you need to compare filenames, for example, copy them to a separate sheet first, so that the program isn't tripping over all the other metadata on top of having to process the conditional formatting.

  • Highlight the cells you want to be included in the comparison
  • From the Home tab, in the Styles section of the menu, choose 'Conditional Formatting' -- > 'Highlight Cells Rules' --> 'Duplicate Values' and click OK
  • To unhighlight, select 'Conditional Formatting' -- 'Clear Rules'

Putting input rules on cells

  • Highlight the cells you want to be under the new rule
  • From the Data tab, in the Data Tools section of the menu, click on 'Data Validation'
  • The following tabs do the following things
    • Settings: lets you say what's an okay value
    • Input Message: lets you write a message the user will see on input -- use only if you're really worried about the input options being clear; if the user knows what to input and you're just making sure he or she does it right, you can leave this message out
    • Error Alert: lets you write a message the user will see if he or she inputs something outside the allowed values
  • Click OK
  • To undo this rule, click on 'Data Validation'

Using Concatenate formula to combine cells or combine a cell with entered data

  • In the first unused column after your data, click on the cell for the first row of data
  • In the Formulas tab, under the Function Library section of the menu, select the Text dropdown, choose 'CONCATENATE'
  • For each text box, either input text or click on the cell you want to copy the contents of; a preview will display below the text boxes
  • Click OK
  • You can drag this cell down through the column to duplicate the formula across multiple rows
  • Note: Since CONCATENATE is a formula, what you see displayed in the cell (product of formula) and what the cell actually contains (formula) are different things; if you copy/paste the new cells or delete the cells you got content from, it will throw off the connection between them. To break this connection, (1) highlight the column with the new cells, right click on it and choose 'copy,' then (2) right click on the same column and choose the second symbol under 'paste options' (clipboard with 123) (or select 'paste special' and choose 'values')

Using Text to Columns to divide the text in a cell into multiple cells

Note: Probably not a good thing to do in the middle of an active metadata spreadsheet; copy to a blank sheet first, do it there, and replace

  • Highlight the cells you want to divide -- hopefully, they're in the same format
  • Under the Data tab, in the Data Tools section of the menu, click on 'Text to Columns'
  • Choose 'Delimited' and click 'Next'
  • Choose from list of 'Delimiters' and look at the Data preview below to see if it divides up properly; if it does, click 'Next'
  • Leave the format on 'General' and click 'Finish'

Advanced Tasks

Sharing a Spreadsheet

  1. Make sure you are the only one who has the spreadsheet open
  2. Go to the Review tab and choose Share Workbook
  3. Select the checkbox for 'Allow changes by more than one user at the same time'
  4. 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.