excel tips and tricks
DESCRIPTION
Excel Tips and Tricks. Important. Learn how to use help!. Excel Tips. Working with Hyperlinks Task: create 2 .xls files, with mutual hyperlinks Shortcut to enter today’s date Ctrl + ;. Excel Tips. Right click status bar, choose ‘quick functions’ Can select multiple non-contiguous cells - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/1.jpg)
Excel Tips and Tricks
PgP MIS 462
![Page 2: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/2.jpg)
Important
• Learn how to use help!
PgP MIS 462
![Page 3: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/3.jpg)
PgP MIS 202
Excel Tips
• Working with Hyperlinks– Task: create 2 .xls files,
with mutual hyperlinks
• Shortcut to enter today’s date– Ctrl + ;
![Page 4: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/4.jpg)
PgP MIS 202
Excel Tips
• Right click status bar, choose ‘quick functions’– Can select multiple non-
contiguous cells
• AutoFill to speed up data entry– Office Button>Excel
Options>Edit Custom Lists…
![Page 5: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/5.jpg)
PgP MIS 202
Excel Tips
• Creating a custom template (.xlt)
• \Program Files\Microsoft Office\Templates\1033
• Seven available- Billing, BloodPressure,Expenses, Loan, Budget, Sales, TimeCard
• Open and examine
![Page 6: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/6.jpg)
PgP MIS 202
Breakeven Analysis
• Search for template(s) at http://office.microsoft.com
• 3 available• Download and examine
“Breakeven analysis”
![Page 7: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/7.jpg)
PgP MIS 202
Custom FormatsTable 23-3. Useful Formatting Characters for Building Custom Number Formats
Character Purpose Example Number Entered
Result
# Creates a placeholder for significant digits, rounding to fit if necessary.
##.### 50.0048 2.30
50.005 2.3
0 Rounds numbers to fit like the # character, but fills any empty positions with zeros to align numbers and to fill all specified positions.
00.00 50.1 5
50.10 05.00
? Also rounds numbers to fit, but fills any empty positions with spaces rather than extra zeros (if necessary) to align numbers and fill positions.
??.?? 5.6 .70 73.27
5.6 .7 73.27
"text" Adds the characters within the quotes to the value in the cell.
"ID " ## 75 2
ID 75 ID 2
comma (,) Separates thousands in numbers. #,### 5600 5,600
$, –, +, :, /, (, ), space
Standard formatting characters. Each appears as specified in the custom numeric format.
$#.000 500.5 $500.500
% Multiplies value by 100 and adds percent sign.
##% .25 25%
![Page 8: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/8.jpg)
PgP MIS 202
Columns & Rows
• What is maximum row height?
• AutoFormat
![Page 9: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/9.jpg)
PgP MIS 202
Conditional Formatting
• Examine CondForm.xls• Find Conditional
Formatting- Edit>GoTo> special…
![Page 10: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/10.jpg)
PgP MIS 202
Page Breaks
• Page Break Preview
![Page 11: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/11.jpg)
Workbook Organization
![Page 12: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/12.jpg)
PgP MIS 202
Understand Locking and Protection• Format>Cells>Protection
tab• All cells initially locked• Add protection to invoke
locking selected cells
![Page 13: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/13.jpg)
PgP MIS 202
Protecting Workbook
• Structure-cannot insert, delete…worksheets
• Windows-prevent resizing
![Page 14: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/14.jpg)
PgP MIS 202
Protecting Worksheets
• Prevent inadvertent changes to formulas…
![Page 15: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/15.jpg)
PgP MIS 202
Worksheet Tabs
• Add names• Change tab colors
![Page 16: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/16.jpg)
PgP MIS 202
InterWorksheet References
• Examine SalesSum.xls
![Page 17: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/17.jpg)
PgP MIS 202
Inter Workbook References
• Examine SalesLnk.xls
![Page 18: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/18.jpg)
PgP MIS 202
Shared Workbooks
• Create one on \\store\classes\200900..\shared
• Experiment with settings, Tools>Share Workbook…
• Create shared workbook on Portal, My Site
![Page 19: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/19.jpg)
Customizing Excel
![Page 20: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/20.jpg)
PgP MIS 202
Custom Views
• For current workbook• Save time• View>Custom Views…
![Page 21: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/21.jpg)
PgP MIS 202
Printing
• Examine tabs in File>Page Setup…
• Scaling, Header/Footer, Print titles, Gridlines, Row and column headings, comments
![Page 22: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/22.jpg)
PgP MIS 202
Panes
• Freeze• Split
![Page 23: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/23.jpg)
PgP MIS 202
Options
• Calculation-F9, Manual, Iteration
• Formulas-R1C1 reference• Custom Lists• Security-Digital
Signatures
![Page 24: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/24.jpg)
PgP MIS 202
Add-Ins, .xla, .xll
• Provide special functionality– Analysis Toolpak– Analysis Toolpak-VBA– Solver
![Page 25: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/25.jpg)
PgP MIS 202
Add-Ins
• Create your own• See MSDN
![Page 26: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/26.jpg)
Formulas and Functions
![Page 27: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/27.jpg)
PgP MIS 202
Formulas vs Results
• Toggle using Ctrl + ~– Formula vs Values
![Page 28: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/28.jpg)
PgP MIS 202
Relative, Absolute and Mixed References• Examine Log.xls from
Companion Content• Highlight formula bar,
cycle references using F4• Do exercise in book,
Building a Formula
![Page 29: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/29.jpg)
PgP MIS 202
Operator Precedence
• Search Help!• Parentheses to override!
![Page 30: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/30.jpg)
PgP MIS 202
Built-In Functions
• Formulas>Insert Function…
• 200 functions by category• Examine arguments
– Note required, optional
![Page 31: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/31.jpg)
PgP MIS 202
Function ErrorsTable 26-3. Common Error Values in Excel Formulas
Error Value
Description
#DIV/0! You’re dividing by zero in this formula. Verify that no cell references refer to blank cells.
#NA You might have omitted a function argument. No value is available.
#NAME? You’re using a name in this formula that hasn’t been defined in the workbook. (See the next section, “Using Names in Functions.”)
#NULL! In a formula, you referred to the intersection of two ranges that don’t intersect.
#NUM! Your function arguments might be out of range or otherwise invalid, or an iterative function you’re using might not have computed long enough to reach a solution. (Entering a rough answer in the Guess argument might reduce the number of iterations Excel needs.)
#REF! Your formula includes range references that have been deleted.
#VALUE! Your formula is using a text entry as an argument.
###### Your calculation results were too wide to fit in the cell. Increase the column width.
![Page 32: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/32.jpg)
PgP MIS 202
Named Ranges
• Simplify Formulas• Formulas>Name Manager• Review section material
carefully, important!
![Page 33: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/33.jpg)
Charts
![Page 34: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/34.jpg)
PgP MIS 202
Charts
• Insert>Chart• Examine SalesSum.xls
![Page 35: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/35.jpg)
PgP MIS 202
Chart Planning
• Data set?• Most other options can be
changed later
![Page 36: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/36.jpg)
PgP MIS 202
Chart Formatting
• Review options, choices• Examine SalesSum.xls
![Page 37: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/37.jpg)
Lists, Filters, PivotTables
![Page 38: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/38.jpg)
PgP MIS 202
Lists
• Examine Pivot.xls• Provided guidelines follow
database theory• Note use of data entry
forms• Review Sorting options
![Page 39: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/39.jpg)
PgP MIS 202
AutoFilter
• Use Pivot.xls, set AutoFilter
• Set up Custom AutoFilter
![Page 40: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/40.jpg)
PgP MIS 202
List Subtotals
• Note levels• Note function subtotals
– Sum, Count, Average…
![Page 41: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/41.jpg)
PgP MIS 202
Converting Lists to .mdb
• Why? Excel has limitations
• Simple to do, use Access, File>Open
![Page 42: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/42.jpg)
PgP MIS 202
PivotTables, PivotCharts
• Data Analysis and Organization
• Experiment with the Wizard using Pivot.xls
![Page 43: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/43.jpg)
PgP MIS 202
Lists in SharePoint
• Create list with Pivot.xls• Save to WSU portal
![Page 44: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/44.jpg)
Business Analysis
![Page 45: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/45.jpg)
PgP MIS 202
Goal Seek
• Examine GoalSeek.xls
![Page 46: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/46.jpg)
PgP MIS 202
Solver
• Examine Solver.xls• Make sure to install Solver
Add-In– Office Button>Excel
Options>Add-Ins…>click Go…
• Named Ranges simplifies understanding
• Follow book example
![Page 47: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/47.jpg)
PgP MIS 202
Scenario Manager for What-If
• Examine Scenario.xls • Scenarios for Best
Case/Worst Case– Scenario reports-Summary
![Page 48: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/48.jpg)
Web Publishing and XML
![Page 49: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/49.jpg)
PgP MIS 202
Excel Web Page
• Be aware of feature limitations
• Static vs dynamic– .html snapshot– .xls file, requires IE 4.01+
![Page 50: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/50.jpg)
PgP MIS 202
Excel Web Page
• Publish Pivot.xls to your web site– Browse to and try to open it– Try to use Solver– Try to use Goal Seek
• What are the benefits?
![Page 51: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/51.jpg)
PgP MIS 202
Excel on the Web
• Web Query– http://moneycentral.msn.co
m/scripts/webquote.dll
• Get Data– Data>From Web
• Modify Data– Data>Connections
• Data refreshed when workbook is opened
![Page 52: Excel Tips and Tricks](https://reader035.vdocuments.us/reader035/viewer/2022081501/56812f2e550346895d94c10f/html5/thumbnails/52.jpg)
PgP MIS 202
XML Data
• XML is a universal, open, nonproprietary, text-based format that is rapidly becoming the common medium for data exchange
• Use Inventory.xml