Working with Microsoft Excel

From UA Libraries Digital Services Planning and Documentation
(Difference between revisions)
Jump to: navigation, search
 
(4 intermediate revisions by 2 users not shown)
Line 1: Line 1:
==If You’ve Never Used Excel==
+
==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.
 
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.
Line 10: Line 10:
 
==Basic Tasks==
 
==Basic Tasks==
  
* '''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 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 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 rows or columns:''' right click on the header for the column or row and choose 'delete'  
* '''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 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 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 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 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 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 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 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 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 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)
  
  
==Helpful Hints For When You’re Working On Metadata==
+
==Intermediate Tasks==
  
===Dealing with repetitive words/phrases===
+
===When importing tab delimited or other .txt files===
  
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!) 
+
* 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.
  
There are several ways of dealing with repetitive data so you don’t have to type it over and over again.
+
===Highlighting duplicates===
  
* '''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.
+
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.
  
* '''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.
+
* 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'
  
* '''Invent your own system of placeholders:'''  Proceed carefully!
+
===Putting input rules on cells===
  
: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.
+
* 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'
  
:Procedure for find/replace:
+
===Using Concatenate formula to combine cells or combine a cell with entered data===
# 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.
+
* 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')
  
===Highlighting cells by formatting cell/text color===
+
===Using Text to Columns to divide the text in a cell into multiple cells===
  
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.
+
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
Start by highlighting the cell(s) you want to format, then do one of the following:
+
* 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'
  
* 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===
+
==Advanced Tasks==
  
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, like Jeremiah, Jody, Marina, Nitin, or Kate.
+
===Sharing a 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.
+
# 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'
  
==Advanced Usage==
+
Unsharing happens in the same manner, except you deselect the checkbox.
# See [http://intranet.lib.ua.edu/wiki/digcoll/images/c/c6/Excel_getting_file_count_from_ContentDM.docx Excel_getting_file_count_from_ContentDM.docx] on how to get a file count from a ContentDM export.
+

Latest revision as of 16:53, 7 July 2014

Contents

[edit] 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.


[edit] 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)


[edit] Intermediate Tasks

[edit] 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.

[edit] 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'

[edit] 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'

[edit] 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')

[edit] 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'


[edit] Advanced Tasks

[edit] 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.

Personal tools