excel tips and tricks
Download
Skip this Video
Download Presentation
Excel Tips and Tricks

Loading in 2 Seconds...

play fullscreen
1 / 52

Excel Tips and Tricks - PowerPoint PPT Presentation


  • 197 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Excel Tips and Tricks' - iliana-neal


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
important
Important
  • Learn how to use help!

PgP MIS 462

excel tips
Excel Tips
  • Working with Hyperlinks
    • Task: create 2 .xls files, with mutual hyperlinks
  • Shortcut to enter today’s date
    • Ctrl + ;

PgP MIS 202

excel tips1
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…

PgP MIS 202

excel tips2
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

PgP MIS 202

breakeven analysis
Breakeven Analysis
  • Search for template(s) at http://office.microsoft.com
  • 3 available
  • Download and examine “Breakeven analysis”

PgP MIS 202

custom formats
Custom Formats

PgP MIS 202

columns rows
Columns & Rows
  • What is maximum row height?
  • AutoFormat

PgP MIS 202

conditional formatting
Conditional Formatting
  • Examine CondForm.xls
  • Find Conditional Formatting- Edit>GoTo> special…

PgP MIS 202

page breaks
Page Breaks
  • Page Break Preview

PgP MIS 202

understand locking and protection
Understand Locking and Protection
  • Format>Cells>Protection tab
  • All cells initially locked
  • Add protection to invoke locking selected cells

PgP MIS 202

protecting workbook
Protecting Workbook
  • Structure-cannot insert, delete…worksheets
  • Windows-prevent resizing

PgP MIS 202

protecting worksheets
Protecting Worksheets
  • Prevent inadvertent changes to formulas…

PgP MIS 202

worksheet tabs
Worksheet Tabs
  • Add names
  • Change tab colors

PgP MIS 202

interworksheet references
InterWorksheet References
  • Examine SalesSum.xls

PgP MIS 202

inter workbook references
Inter Workbook References
  • Examine SalesLnk.xls

PgP MIS 202

shared workbooks
Shared Workbooks
  • Create one on \\store\classes\200900..\shared
  • Experiment with settings, Tools>Share Workbook…
  • Create shared workbook on Portal, My Site

PgP MIS 202

custom views
Custom Views
  • For current workbook
  • Save time
  • View>Custom Views…

PgP MIS 202

printing
Printing
  • Examine tabs in File>Page Setup…
  • Scaling, Header/Footer, Print titles, Gridlines, Row and column headings, comments

PgP MIS 202

panes
Panes
  • Freeze
  • Split

PgP MIS 202

options
Options
  • Calculation-F9, Manual, Iteration
  • Formulas-R1C1 reference
  • Custom Lists
  • Security-Digital Signatures

PgP MIS 202

add ins xla xll
Add-Ins, .xla, .xll
  • Provide special functionality
    • Analysis Toolpak
    • Analysis Toolpak-VBA
    • Solver

PgP MIS 202

add ins
Add-Ins
  • Create your own
  • See MSDN

PgP MIS 202

formulas vs results
Formulas vs Results
  • Toggle using Ctrl + ~
    • Formula vs Values

PgP MIS 202

relative absolute and mixed references
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

PgP MIS 202

operator precedence
Operator Precedence
  • Search Help!
  • Parentheses to override!

PgP MIS 202

built in functions
Built-In Functions
  • Formulas>Insert Function…
  • 200 functions by category
  • Examine arguments
    • Note required, optional

PgP MIS 202

function errors
Function Errors

PgP MIS 202

named ranges
Named Ranges
  • Simplify Formulas
  • Formulas>Name Manager
  • Review section material carefully, important!

PgP MIS 202

charts1
Charts
  • Insert>Chart
  • Examine SalesSum.xls

PgP MIS 202

chart planning
Chart Planning
  • Data set?
  • Most other options can be changed later

PgP MIS 202

chart formatting
Chart Formatting
  • Review options, choices
  • Examine SalesSum.xls

PgP MIS 202

lists
Lists
  • Examine Pivot.xls
  • Provided guidelines follow database theory
  • Note use of data entry forms
  • Review Sorting options

PgP MIS 202

autofilter
AutoFilter
  • Use Pivot.xls, set AutoFilter
  • Set up Custom AutoFilter

PgP MIS 202

list subtotals
List Subtotals
  • Note levels
  • Note function subtotals
    • Sum, Count, Average…

PgP MIS 202

converting lists to mdb
Converting Lists to .mdb
  • Why? Excel has limitations
  • Simple to do, use Access, File>Open

PgP MIS 202

pivottables pivotcharts
PivotTables, PivotCharts
  • Data Analysis and Organization
  • Experiment with the Wizard using Pivot.xls

PgP MIS 202

lists in sharepoint
Lists in SharePoint
  • Create list with Pivot.xls
  • Save to WSU portal

PgP MIS 202

goal seek
Goal Seek
  • Examine GoalSeek.xls

PgP MIS 202

solver
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

PgP MIS 202

scenario manager for what if
Scenario Manager for What-If
  • Examine Scenario.xls
  • Scenarios for Best Case/Worst Case
    • Scenario reports-Summary

PgP MIS 202

excel web page
Excel Web Page
  • Be aware of feature limitations
  • Static vs dynamic
    • .html snapshot
    • .xls file, requires IE 4.01+

PgP MIS 202

excel web page1
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?

PgP MIS 202

excel on the web
Excel on the Web
  • Web Query
    • http://moneycentral.msn.com/scripts/webquote.dll
  • Get Data
    • Data>From Web
  • Modify Data
    • Data>Connections
  • Data refreshed when workbook is opened

PgP MIS 202

xml data
XML Data
  • XML is a universal, open, nonproprietary, text-based format that is rapidly becoming the common medium for data exchange
  • Use Inventory.xml

PgP MIS 202

ad