Excel tips and tricks
Sponsored Links
This presentation is the property of its rightful owner.
1 / 52

Excel Tips and Tricks PowerPoint PPT Presentation


  • 163 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Excel Tips and Tricks

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


Excel Tips and Tricks

PgP MIS 462


Important

  • Learn how to use help!

PgP MIS 462


Excel Tips

  • Working with Hyperlinks

    • Task: create 2 .xls files, with mutual hyperlinks

  • Shortcut to enter today’s date

    • Ctrl + ;

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…

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

PgP MIS 202


Breakeven Analysis

  • Search for template(s) at http://office.microsoft.com

  • 3 available

  • Download and examine “Breakeven analysis”

PgP MIS 202


Custom Formats

PgP MIS 202


Columns & Rows

  • What is maximum row height?

  • AutoFormat

PgP MIS 202


Conditional Formatting

  • Examine CondForm.xls

  • Find Conditional Formatting- Edit>GoTo> special…

PgP MIS 202


Page Breaks

  • Page Break Preview

PgP MIS 202


Workbook Organization


Understand Locking and Protection

  • Format>Cells>Protection tab

  • All cells initially locked

  • Add protection to invoke locking selected cells

PgP MIS 202


Protecting Workbook

  • Structure-cannot insert, delete…worksheets

  • Windows-prevent resizing

PgP MIS 202


Protecting Worksheets

  • Prevent inadvertent changes to formulas…

PgP MIS 202


Worksheet Tabs

  • Add names

  • Change tab colors

PgP MIS 202


InterWorksheet References

  • Examine SalesSum.xls

PgP MIS 202


Inter Workbook References

  • Examine SalesLnk.xls

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

PgP MIS 202


Customizing Excel


Custom Views

  • For current workbook

  • Save time

  • View>Custom Views…

PgP MIS 202


Printing

  • Examine tabs in File>Page Setup…

  • Scaling, Header/Footer, Print titles, Gridlines, Row and column headings, comments

PgP MIS 202


Panes

  • Freeze

  • Split

PgP MIS 202


Options

  • Calculation-F9, Manual, Iteration

  • Formulas-R1C1 reference

  • Custom Lists

  • Security-Digital Signatures

PgP MIS 202


Add-Ins, .xla, .xll

  • Provide special functionality

    • Analysis Toolpak

    • Analysis Toolpak-VBA

    • Solver

PgP MIS 202


Add-Ins

  • Create your own

  • See MSDN

PgP MIS 202


Formulas and Functions


Formulas vs Results

  • Toggle using Ctrl + ~

    • Formula vs Values

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

PgP MIS 202


Operator Precedence

  • Search Help!

  • Parentheses to override!

PgP MIS 202


Built-In Functions

  • Formulas>Insert Function…

  • 200 functions by category

  • Examine arguments

    • Note required, optional

PgP MIS 202


Function Errors

PgP MIS 202


Named Ranges

  • Simplify Formulas

  • Formulas>Name Manager

  • Review section material carefully, important!

PgP MIS 202


Charts


Charts

  • Insert>Chart

  • Examine SalesSum.xls

PgP MIS 202


Chart Planning

  • Data set?

  • Most other options can be changed later

PgP MIS 202


Chart Formatting

  • Review options, choices

  • Examine SalesSum.xls

PgP MIS 202


Lists, Filters, PivotTables


Lists

  • Examine Pivot.xls

  • Provided guidelines follow database theory

  • Note use of data entry forms

  • Review Sorting options

PgP MIS 202


AutoFilter

  • Use Pivot.xls, set AutoFilter

  • Set up Custom AutoFilter

PgP MIS 202


List Subtotals

  • Note levels

  • Note function subtotals

    • Sum, Count, Average…

PgP MIS 202


Converting Lists to .mdb

  • Why? Excel has limitations

  • Simple to do, use Access, File>Open

PgP MIS 202


PivotTables, PivotCharts

  • Data Analysis and Organization

  • Experiment with the Wizard using Pivot.xls

PgP MIS 202


Lists in SharePoint

  • Create list with Pivot.xls

  • Save to WSU portal

PgP MIS 202


Business Analysis


Goal Seek

  • Examine GoalSeek.xls

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

PgP MIS 202


Scenario Manager for What-If

  • Examine Scenario.xls

  • Scenarios for Best Case/Worst Case

    • Scenario reports-Summary

PgP MIS 202


Web Publishing and XML


Excel Web Page

  • Be aware of feature limitations

  • Static vs dynamic

    • .html snapshot

    • .xls file, requires IE 4.01+

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?

PgP MIS 202


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 is a universal, open, nonproprietary, text-based format that is rapidly becoming the common medium for data exchange

  • Use Inventory.xml

PgP MIS 202


  • Login