Excel tips and tricks
Download
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