Working with Microsoft Excel - Revision history

Kgmatheny: added section on sharing spreadsheets

Kgmatheny — Tue, 12 Mar 2013 14:05:21 GMT

added section on sharing spreadsheets

←Older revision		Revision as of 14:05, 12 March 2013
Line 75:		Line 75:

	==Advanced Usage==		==Advanced Usage==
		+
		+	===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
		+
		+
		+	==Other Notes==
	# 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.		# 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.
	As of 2010, this is somewhat irrelevant given our shift to [http://acumen.lib.ua.edu Acumen], still this tutorial has some techniques that can be applied to other situations.		As of 2010, this is somewhat irrelevant given our shift to [http://acumen.lib.ua.edu Acumen], still this tutorial has some techniques that can be applied to other situations.

Kgmatheny: Added section on importing text files

Kgmatheny — Tue, 06 Nov 2012 21:52:03 GMT

Added section on importing text files

←Older revision		Revision as of 21:52, 6 November 2012
Line 18:		Line 18:
	* '''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’
-

	==Helpful Hints For When You’re Working On Metadata==		==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.

	===Dealing with repetitive words/phrases===		===Dealing with repetitive words/phrases===

Scpstu32: /* Advanced Usage */

Scpstu32 — Thu, 19 Aug 2010 19:51:21 GMT

Advanced Usage

←Older revision		Revision as of 19:51, 19 August 2010
Line 62:		Line 62:
	==Advanced Usage==		==Advanced Usage==
	# 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.		# 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.
		+	As of 2010, this is somewhat irrelevant given our shift to [http://acumen.lib.ua.edu Acumen], still this tutorial has some techniques that can be applied to other situations.

Scpstu32: /* Adding a comment to a cell */

Scpstu32 — Thu, 19 Aug 2010 19:49:39 GMT

Adding a comment to a cell

←Older revision		Revision as of 19:49, 19 August 2010
Line 56:		Line 56:
	===Adding a comment to a cell===		===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~~, like Jeremiah, Jody, Marina, Nitin, or Kate~~.	+	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.		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.

Scpstu32 at 22:01, 17 September 2009

Scpstu32 — Thu, 17 Sep 2009 22:01:02 GMT

←Older revision		Revision as of 22:01, 17 September 2009
Line 60:		Line 60:
	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.		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.

-	===Advanced Usage===	+	==Advanced Usage==
-	# See ~~this document~~ on how to get a file count from a ContentDM export.	+	# 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.

Scpstu32 at 21:57, 17 September 2009

Scpstu32 — Thu, 17 Sep 2009 21:57:05 GMT

←Older revision		Revision as of 21:57, 17 September 2009
Line 59:		Line 59:

	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.		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.
		+
		+	===Advanced Usage===
		+	# See this document on how to get a file count from a ContentDM export.

Scpstu16 at 16:38, 30 September 2008

Scpstu16 — Tue, 30 Sep 2008 16:38:01 GMT

←Older revision		Revision as of 16:38, 30 September 2008
Line 1:		Line 1:
-	~~==MICROSOFT EXCEL BASICS==~~	+	==If You’ve Never Used 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.		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 8:


-	===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 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.
Line 22:		Line 20:


-	===Helpful Hints For When You’re Working On Metadata===	+	==Helpful Hints For When You’re Working On Metadata==

-	====Dealing with repetitive words/phrases====	+	===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!)		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!)
Line 47:		Line 45:
	: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.		: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====	+	===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.		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.
Line 56:		Line 54:
	* Right click selected cells and the ‘font’ menu will pop up. Follow procedures from # 2.		* Right click selected cells and the ‘font’ menu will pop up. Follow procedures from # 2.

-	====Adding a comment to a cell====	+	===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, like Jeremiah, Jody, Marina, Nitin, or Kate.		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.

	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.		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.

Scpstu16 at 16:35, 30 September 2008

Scpstu16 — Tue, 30 Sep 2008 16:35:42 GMT

←Older revision		Revision as of 16:35, 30 September 2008
Line 8:		Line 8:

	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.		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.
		+

	===Basic Tasks===		===Basic Tasks===
Line 19:		Line 20:
	* '''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’
		+

	===Helpful Hints For When You’re Working On Metadata===		===Helpful Hints For When You’re Working On Metadata===

Scpstu16 at 16:34, 30 September 2008

Scpstu16 — Tue, 30 Sep 2008 16:34:23 GMT

←Older revision		Revision as of 16:34, 30 September 2008
Line 34:		Line 34:
	* '''Invent your own system of placeholders:''' Proceed carefully!		* '''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.	+	: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:	+	:Procedure for find/replace:
	# From the ‘Home’ tab, look at the ‘Find & Select’ menu and click ‘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.’		# Input your abbreviation in ‘Find what’ and your longer phrase in ‘Replace with.’
Line 43:		Line 43:
	# Close the ‘Find and Replace’ screen.		# 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.	+	: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====		====Highlighting cells by formatting cell/text color====

Scpstu16 at 16:33, 30 September 2008

Scpstu16 — Tue, 30 Sep 2008 16:33:40 GMT

←Older revision		Revision as of 16:33, 30 September 2008
Line 34:		Line 34:
	* '''Invent your own system of placeholders:''' Proceed carefully!		* '''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.	+	*: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:	+	*:Procedure for find/replace:
	# From the ‘Home’ tab, look at the ‘Find & Select’ menu and click ‘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.’		# Input your abbreviation in ‘Find what’ and your longer phrase in ‘Replace with.’
Line 43:		Line 43:
	# Close the ‘Find and Replace’ screen.		# 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.	+	*: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====		====Highlighting cells by formatting cell/text color====