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

Excel Tips and Tricks PowerPoint PPT Presentation


  • 147 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

Excel Tips and Tricks

PgP MIS 462


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


Workbook organization

Workbook Organization


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


Customizing excel

Customizing Excel


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 and functions

Formulas and Functions


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


Charts

Charts


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 filters pivottables

Lists, Filters, PivotTables


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


Business analysis

Business Analysis


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


Web publishing and xml

Web Publishing and XML


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


  • Login