Effective Naming and Referencing in Excel Spreadsheets
Enhance your Excel skills by learning the importance of naming variables, using named ranges, and confirming label locations. Discover how to create named cells, improve formula readability, and optimize spreadsheet organization. Excel proficiency awaits with these techniques!
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
EXCEL IN M.E. PART I Keith A. Woodbury Mechanical Engineering University of Alabama
NAMING VARIABLES Using cell references can be difficult when working with formulas and spreadsheets References like A1 and G47 don t mean much By naming the cell it becomes easier to identify a parameter in a formula or function call Assigning natural names to parameters makes it easier to find mistakes
NAMING VARIABLES - EXAMPLE Not a good way to code Not a good way to code formulas. Although F2 formulas. Although F2 reveals which cells are reveals which cells are involved in the computation, involved in the computation, a look at the formula does a look at the formula does not immediately reveal what not immediately reveal what variables are involved. variables are involved.
Named Ranges Instead, use names for the variables Choose names that are longer than two characters so Excel doesn t confuse them with cell addresses Choose P_1 instead of P1 Or Press instead of P
NAMING VARIABLES - EXAMPLE Create named cells to Create named cells to use in formulas use in formulas This is on the This is on the Formulas ribbon in Formulas ribbon in Excel 2007 Excel 2007 Highlight cells Highlight cells with names with names and values in and values in adjacent cells adjacent cells
Confirm location of labels
Now the labels can Now the labels can be used as be used as parameters in the parameters in the formula formula
NAMED RANGE - EXAMPLE Named Cells can also be Named Cells can also be ranges ranges (vectors); in this (vectors); in this case the named cells are case the named cells are in columns or rows in columns or rows
NAMED RANGE - EXAMPLE Now the Now the formula is formula is readily human readily human- - readable readable Excel uses the Excel uses the entry in the vector entry in the vector on the same row on the same row as the formula as the formula
DOCUMENTING SPREADSHEETS It is easier when working with formulas in Excel to document them. Formulas are shown next to computed result Mistakes can be spotted quickly Documentation should also make printed copies of the file understandable to knowledgeable person (teacher or colleague) This process involves copying formulas into cells or using Excel s Tools tab.
Suggested Convention Use the first column for labels for the values Use the second column for the given value or formula to compute the result Put the units of the quantity in the third column Use the fourth column to cut-and-paste the text of the formula, or to provide additional explanatory information
DOCUMENTING SS - EXAMPLE First use F2 to First use F2 to highlight show the highlight show the formula for the formula for the cell cell
Now use keystroke Now use keystroke SHIFT+HOME to SHIFT+HOME to highlight the cell entry highlight the cell entry
Then use CTRL+c to cut the Then use CTRL+c to cut the text to the clipboard buffer, text to the clipboard buffer, followed by ESC to get out of followed by ESC to get out of F2 mode F2 mode
Now move over to the fourth Now move over to the fourth colum, put a leading tick colum, put a leading tick- - mark ( ) so Excel will treat as mark ( ) so Excel will treat as text (not formula), then use text (not formula), then use CTRL CTRL- -v to paste the v to paste the clipboard contents clipboard contents
Secret Jedi Keystroke sequence Highlight formula cell F2 to get equation displayed SHIFT-HOME to highlight CTRL-c to copy ESC to get out of F2 mode Move to fourth column Tick mark to treat paste as text CTRL-v to paste the formula
