Excel zone

Feature

The ExcelZone Compendium 2007

CONTENTS
  • ExcelZone Hall of Fame
  • Excel tips and resources
  • Spreadsheet design & management
  • Data entry
  • Navigation and shortcuts
  • Formatting
  • Dates & Numbers
  • Sort & Filter
  • Graphs & Charts
  • Printing
  • Worksheets & Ranges
  • Pivot Tables
  • Importing & Exporting
  • ODBC and Query
  • Functions & Formulae
  • SUMIF & IF
  • Lookups
  • Macros
  • Visual Basic programming
  • Hide & Protect
  • Excel 2007
  • Excel applications and templates
  • Working with accounts data
  • Linking to other applications
  • Network issues and Excel Services
  • Troubleshooting
  • ExcelZone links

    • The ExcelZone Hall of Fame

  • Top 20 Excel tips from Chris Bales (2000)
  • Gail Perry's Excel Almanac - Top Productivity Tips (2000-01)
  • Excel navigation tips from AccountingWEB members
  • Excel spreadsheets in budgeting Peter Wolstenholme's horror story
  • Spreadsheet errors and how to avoid them by Ray Butler, Customs & Excise
  • Build your own Excel Net to Gross function Simon Hurst tutorial
  • Data validation for better spreadsheets
  • An introduction to Excel-driven reporting tools
  • Want to learn about Excel pivot tables? Start here
  • Improve your reporting skills with self-teach tutorials
  • David Carter's two-minute Excel tips
  • David Carter's five-minute pivot table tips
  • Excel templates - buried treasure on Microsoft.com
  • ExcelZone Compendium Archive 1999-2007 - original listing of 500+ queries, tips & tutorials
  • ExcelZone links

    • Back to Index

    Excel tips and resources

  • Top 20 Excel tips from Chris Bales (2000)
  • Gail Perry's Excel Almanac - Top Productivity Tips (2000-01)
  • Excel Training
  • Lunchtime learning - online Excel training seminar with Simon Hurst
  • Online Excel seminar tackles macros, VBA and pivot tables
  • More ProductivITy Tips from Simon Hurst
  • Navigation tips from AccountingWEB members
  • Excel templates - buried treasure on Microsoft.com
  • Webcast on financial presentations in Excel and other aids
  • Excel web services tutorials on MSDN

    • Back to Index

    Spreadsheet design and management

  • Excel Essentials - Five key rules of spreadsheet design
  • Spreadsheet errors and how to avoid them
  • Don't mix your Excel data
  • Data validation for better spreadsheets
  • Simon Hurst's practice IT casebook: Excess Excel
  • Hurst on spreadsheet reports: "Think first, Excel second"
  • The Bloor Research guide to spreadsheet compliance tools

    • Back to Index

    Data entry

  • ExcelZone Compendium: data entry basics
  • ExcelZone Compendium: Advanced data entry tips
  • Tips to manage and speed up data entry (Excel 2007 update)
  • Data validation for better spreadsheets
  • ProductivITy Tips: Columns & rows
  • Excel Tip: Copy an entire worksheet
  • Excel Tip: Perform fast calculations
  • ExcelZone Compendium Archive - more data entry queries and answers

    • Back to Index

    Shortcuts and navigation

  • Excel 2007 Tips: Find your way around the new system
  • ExcelZone Compendium: Navigation tips & shortcuts (Excel 2003 and previous versions)
  • ExcelZone: Navigation tips from AccountingWEB members
  • Five simple tips to save time and pain with Excel
  • ProductivITy Tips: Columns & rows
  • 'Be prepared to be amazed' - Microsoft's Excel tips
  • Microsoft's 'Did you know?' guide for Excel
  • Microsoft Office Online keyboard shortcuts
  • Microsoft Office Online - Excel shortcut and function keys
  • Charlie's List, or "214 Keyboard Shortcuts" Unofficial Microsoft listing with Excel 2007 shortcuts
  • Shortcut Keys in Excel 2000 - Maintained by David McRitchie
  • Everybody knows about F2 in Excel
  • ExcelZone Compendium Archive - more navigation queries and answers

    • Back to Index

    Formatting

  • ExcelZone Compendium: Formatting tipss (Excel 2007 update)
  • ExcelZone Compendium: Formatting digest (Excel 2003 and previous versions)
  • ProductivITy Tips: Columns & rows
  • Excel 2007 - Get ahead with conditional formatting
  • Excel 2007 Tables - Not just a pretty format
  • Excel: colour coding tabs
  • Colouring cells in an Excel spreadsheet dependent on a condition
  • Cell formatting in Excel
  • P&L in Excel - how do I suppress current month?
  • Merging columns using CONCATENATE in Excel
  • Excel Check Boxes in Questionaires
  • ExcelZone Compendium Archive - more formatting queries and answers

    • Back to Index

    Dates & Numbers

  • ExcelZone Compendium: Dates & numbers digest
  • Excel Tip: Dealing With Rounding Errors
  • Century break - when your Excel date just doesn't work out
  • ExcelZone Compendium Archive - more date & number queries and answers

    • Back to Index

    Sort & Filter

  • ExcelZone Compendium - sorting & filtering tips (Excel 2003 and previous versions)
  • ExcelZone Compendium: Sorting and filtering tips (Excel 2007 update)
  • Excel sequence check
  • Finding duplicate records in lists in Excel
  • Excel Autofilter advice needed...
  • How to filter data from left to right in Excel?
  • Data Sort in protected worksheets
  • Finding non zero values in Excel
  • Excel List Selection
  • Matching two pieces of data
  • Is there an Excel function for "Contains"
  • Find a row number in a sorted list
  • Filtering tips
  • Identifing variables between higher & lower values
  • ExcelZone Compendium Archive - more sort & filter queries and answers

    • Back to Index

    Graphs & Charts

  • ExcelZone Compendium: Charts & graphs (Excel 2003 and previous versions)
  • Charts and graphs made simple (Excel 2007 update)
  • Excel tutorial - Charting business performance
  • Tips on how to improve your Excel chart formats
  • Graphing a summary of daily statistics
  • Pie Charts
  • Tutorial: Exploring PivotChart options
  • Excel and graphical charts
  • Plotting customers on a map
  • ExcelZone Compendium Archive - more chart & graph queries and answers

    • Back to Index

    Printing

  • ExcelZone Compendium - printing tips (Excel 2003 and previous versions)
  • Printing tips (Excel 2007 update)
  • How to put "&" in header/footer of Excel sheet
  • Showing comments on screen and printing
  • Print suppression in Excel
  • Excel - how do you print a watermark?
  • Excel won't print landscape
  • Excel - Page Setup - Custom footer
  • Printing multiple columns
  • Template settings
  • Page footer sub totals
  • Print Settings
  • Print a spreadsheet on a different printer
  • Print alternate pages
  • Paper size
  • Headers and footers
  • Envelope printing in Excel
  • Set print area in Excel 2003
  • ExcelZone Compendium Archive - more printing queries and answers

    • Back to Index

    Worksheets & Ranges

  • ExcelZone Compendium: Working with worksheets (Excel 2007 update)
  • Excel Compendium digest - worksheets and ranges (Excel 2003 and previous)
  • Manipulating sheets in Excel, Part 1
  • Manipulating sheets in Excel, Part 2
  • New Range Name features in Excel 2007
  • 10 Quick Range Name Tricks
  • VBA tutorial: Triggering a worksheet change event
  • Why does Excel change links to files?
  • Managing 3D spreadsheets
  • File combine facilities: Excel v Lotus
  • Can you "synchronise" two sheets?
  • How can one remove redundant links from Excel
  • Sorting Excel Sheets alphabetically
  • Copying links from worksheet cells to a master
  • Linked, changeable Range in a formula
  • Summarising multiple workbooks
  • Combining lots of spreadsheets into one
  • Breaking up a spreadsheet
  • Excel destination key
  • Excel data transfers in worksheets
  • Data Analysis in Excel
  • Copy worksheets to another
  • Worksheet tabs
  • Calculations (auto F9 in Excel XP)
  • How to combine three worksheets into one for a pivot table
  • Revise ranges in Excel
  • ExcelZone Compendium Archive - more worksheet & range queries and answers

    • Back to Index

    Pivot Tables

  • Want to learn about Excel pivot tables? Start here
  • David Carter's Five Minute Pivot Table Tips
  • Excel 2007 - A new approach to PivotTables
  • Budgeting with Pivot tables (Excel 5/97)
  • Budgeting with Pivot Tables - Tutorial 2 (Excel 5/97)
  • Analyse sales data with Excel pivot tables
  • Sage Line 50 and Pivot Tables tutorial
  • Audit and financial reporting with pivot tables tutorial
  • Modelling and What if? analysis with pivot tables - Tutorial
  • Consolidating multiple worksheets in a Pivot Table tutorial
  • Excel 2000/XP pivot table tutorial - Budgeting
  • Tutorial 2 - Sales Analysis
  • Online Excel seminar tackles macros, VBA and pivot tables
  • PivotTable Resource planner
  • Resoucer planning appendix
  • Tutorial: Exploring PivotChart options
  • Custom sort on pivot table columns
  • Excel Pivot Table Selection
  • Macro to change pages
  • Consolidate - only one row and column
  • Pivot does Count, not Sum
  • Autofill - How do I get a summary of categories?
  • Customise sort order
  • Show empty columns in a pivot table
  • Pivot table values still there after delete
  • Consolidation budgets in PivotTables
  • Make formatting stick in PivotTables
  • Pivots across pages
  • Pivot tables formatting (double click sheet)
  • Number formatting in PivotTables
  • Can't get my pivot table to produce two grand totals
  • Can't group dates in pivot table
  • How do I calculate margin percentage in a pivot table?
  • Cumulative totals in pivot tables for non-December year-ends
  • ExcelZone Compendium Archive - more PivotTable queries and answers

    • Back to Index

    Importing & Exporting

  • Working with external data (Excel 2007 update)
  • ExcelZone Compendium - importing & exporting digest (Excel 2003 and earlier versions)
  • Exporting multiple Excel sheets to .csv?
  • Importing data results in negative zeroes!
  • Analysing exported data within Excel
  • Excel - How to convert text representing a negative
  • Import account 0021 and 311 = date 31 Jan
  • Export dates wrong format
  • Export tab-separated list
  • Text import
  • OCR scanning into Excel
  • How can I convert a PDF file to Excel?
  • ExcelZone Compendium Archive - more import & export queries and answers

    • Back to Index

    ODBC and Query

  • Use MSQuery to extract and analyse accounts data
  • Sage data via ODBC
  • Excel and Microsoft Query
  • Excel Calculate
  • Lotus to Excel import
  • Microsoft Query manual
  • Pivots and Query
  • Numbers appears as Text with OBDC link?
  • Autorefresh in Sage ODBC

    • Back to Index

    Functions & Formulae

  • Tutorial: Understanding Excel formulae
  • ExcelZone Digest: Using functions and formulae
  • Funky Functions: Useful gems in Excel's Analysis Pack
  • Fun with Excel functions: OFFSET
  • Fun with Excel functions: ABS, SUMIF and the array formulae
  • Tutorial: Linking sheets using the Indirect function
  • Weighted average formula
  • SUMPRODUCT
  • Excel NETWORKDAYS function
  • Excel - can you have an intentional circular reference?
  • Excel formula for calculating Debtors & Credit
  • Excel 3D formulas
  • Nested functions in Excel?
  • Cell reference as header/footer
  • ROUND formula
  • Indirect function cell ref in a formula
  • Extracting values from a string
  • Transpose function
  • Function wizard in XP
  • GetPivotData function in Excel
  • How can I test text for a string?
  • Excel financial functions
  • ExcelZone Compendium Archive - more function & formula queries and answers

    • Back to Index

    SUMIF & IF

  • ExcelZone Compendium - Practical tips for using SUMIF() and IF()
  • How to use IF expression
  • Fun with Excel functions: ABS, SUMIF and the array formulae
  • Excel 2007 SUMIFS(): The accountant's favourite Excel function gets an overhaul
  • SUMIF & AND functions
  • 'Countif' function in Excel
  • If statements
  • SUMIFs linking to other workbooks
  • SUMIF puzzler with ranges
  • TimeSheet spreadsheet & multiple IF query
  • ExcelZone Compendium Archive - more SUMIF & IF queries and answers

    • Back to Index

    Lookup

  • Practical uses for Lookup and Reference functions
  • Sort of a two variable Lookup table
  • Mapping accounts in Excel
  • Lookup in Excel
  • Excel: Using Match & Index to replace Vlookup
  • How can I stop getting #Div/0
  • Using Excel’s Vlookup function to add fields
  • Vlookup gives wrong answer
  • ExcelZone Compendium Archive - more Lookup queries and answers

    • Back to Index

    Macros

  • ProductivITy Tip: Tackling a simple Excel macro
  • Tutorial: Build your own Excel Net to Gross function
  • Online Excel seminar tackles macros, VBA and pivot tables
  • Emulating Excel's Autosum feature as a macro
  • Macros in Excel 97
  • Self-running macros in Excel
  • Excel 97: How do I automate running sensitivities ?
  • Opening an Excel file
  • Excel Macro Problem
  • Excel Macro Question
  • Excel macro query
  • Macros for Footers in Excel
  • Distribute Excel macro
  • Simple One Buton Macro for Watermarks
  • Excel Macro - Printer selection
  • Excel graphs and auto macros
  • Excel Macro Running Problem
  • Opening a specified sheet
  • Running Macros with if statement
  • Excel auto copy macro
  • Macros slow in XP
  • Macro for copying dates
  • Can you assign two macros to the same button?
  • Macros: how do I activate on opening the workbook?
  • Macro to update form number when it's printed

    • Back to Index

    Visual Basic programming

  • Using Visual Basic to copy a whole cell within a table
  • VBA tutorial: Triggering a worksheet change event
  • Excel VBA tip o' th' day
  • Why Copy/Paste macro - works in VBE but not in Excel?
  • Visual basic - Macro
  • Excel VBA for extracting from a range in a list
  • Copy cells (with fomat/formula) using VB Code
  • Spin button in Excel
  • VB script required
  • Excel VBA code to find empty area
  • VBA routine to save file based on sheet name
  • VB code to identify column where active cell is located

    • Back to Index

    Hide & Protect

  • Excel Compendium Digest: Using Hide and Protect
  • Excel - prevent hidden columns being seen
  • Hiding formulae in Excel
  • Password box in Excel
  • UnProtecting Objects in Excel
  • Excel AutoFilter command & worksheet protection
  • Protect formulae in a template
  • Unhiding multiple worksheets in Excel
  • Controlled Stationery - protecting cells
  • Protected spreadsheet rows
  • ExcelZone Compendium Archive - more sort & filter queries and answers

    • Back to Index

    Excel 2007

  • Focus on Excel 2007
  • Excel 2007 Tips: Find your way around the new system
  • Excel 2007: Is the upgrade worth it?
  • Excel 2007 unlikely to please accountants
  • The Excel 2007 interface - Case for the defence, Part 1 by David Carter
  • The Excel 2007 interface - Case for the defence, Part 2
  • Excel 2007 look and feel previewed
  • Excel 2007 - A developer's view
  • Office 2007 six months on: Where are the baked beans?
  • Microsoft offers Excel 2007 migration guide
  • The $34,465 Excel 2007 calculation bug
  • Excel 2007 Tables - Not just a pretty format
  • Excel 2007 - A new approach to PivotTables
  • Excel 2007 - Get ahead with conditional formatting
  • Excel 2007 SUMIFS(): The accountant's favourite Excel function gets an overhaul
  • New Range Name features in Excel 2007
  • Where's the Macro command in Excel 2007?' - interactive guide online
  • New Excel 2007 templates from spreadsheet users
  • A power-user's guide to boosting Excel 2007 performance

    • Back to Index

    Excel applications and templates

  • Automatic invoice generation
  • Build your own Excel Net to Gross function - Tutorial
  • Use Excel to cut your BT phone bill - by Peter Hool
  • Excel spreadsheet to calculate corporation tax
  • IRR spreadsheet setup
  • Calculating Mortgage Repayments on Excel
  • Excel formats for Limited Liability Partnerships
  • Python and Excel
  • Loan Manager - Excel 2000
  • XL Cubed?
  • Scanning cheque numbers into Excel
  • Excel NPV calc
  • Formula for compound interest
  • Help - Cash flow model required
  • Excel invoice templates
  • Leaseback model or formula in excel
  • Club accounts

    • Back to Index

    Working with accounts data

  • Improve your reporting skills with self-teach tutorials
  • Use MSQuery to extract and analyse accounts data
  • Tutorial: Using Excel with downloaded bank statements
  • Reporting against Budget with pivot tables
  • Tips on Exporting from Sage Line 50 into Excel
  • Tips on Exporting from Sage into Excel – Part 2
  • Exporting Sage data Part 3 – Building a P&L in Excel
  • Can you get a good daybook out of QuickBooks?
  • Sage departmental Excel reporting
  • How to use Excel for bank reconciliation
  • Reporting project P&Ls in Excel
  • How can you export a sales daybook from QuickBooks?
  • Nested sub-totals
  • MS Access Northwind sales data tutorial 1: Improving sales reports at Northwind Traders
  • MS Access Northwind sales data tutorial 2: Creating the daybook
  • MS Access Northwind sales data tutorial 3: Create sales reports with pivot tables
  • MS Access Northwind sales data tutorial 4 : Add cost prices and margins

    • Back to Index

    Linking to other applications

  • The best spreadsheet Add-In? Microsoft Access
  • Excel clinic: Link drop-down menus to MS Access
  • Analytic applications for business - Excel reporting tools and Add-Ins
  • An introduction to Excel-driven reporting tools
  • Excel hyperlink screentips
  • Excel to Access Convertion
  • Moving data between systems using Access and Excel
  • Converting Lotus to Microsoft
  • PC Excel model to use on Mac
  • Using Lotus 1-2-3 Formulas in Excel
  • Microsoft Excel/Word problem
  • Can Excel fire up Outlook?
  • Linking Excel spreadsheets to Word.
  • Date problems in PC-Mac migration
  • Linking Excel into Word
  • Combining Excel with Word
  • Quattro Pro conversion
  • Mailshot and Word/text to columns
  • Copying Excel into Word
  • Mail merge

    • Network issues and Excel Services

  • Excel files on server
  • Excel web services tutorials on MSDN

    • Back to Index

    Troubleshooting

  • Huge Excel Workbook
  • File size
  • Century break - when your Excel date just doesn't work out
  • Excel files have become read only
  • Illegal Operation in Excel
  • Excel file has become unopenable
  • Data Integrity Check on MS Excel
  • Excel File is corrupt: Help Needed
  • Excel slow to open a blank file
  • Problem opening file
  • Excel 97 crashes in Excel 2002
  • Duplication of Excel files
  • File corruption
  • Formula problem
  • Excel on XP?
  • Excel upgrade?
  • File repairs, anyone?
  • Recover lost file
  • Mouse not working
  • Storage problem - lots of RAM
  • Problem saving Excel 2000
  • Lost the data entry at top of sheet
  • Screen handing in Excel
  • Cannot open Template Wizard

    • Back to Index

    Subscribe to the ExcelZone newswire
    Subscribe to the ExcelZone NewswireTo keep up with spreadsheet issues, click the graphic to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

    Number of comments: 2

    AccountingWEB.co.uk 3-Dec-2007
    Categories: IT Features, ExcelZone Features
    Times read: 14222

    User Comment Philip Hammond, 23-Jul-2008

    Date Rounding
    Having a small problem with Date rounding. I am trying to insert a VLOOKUP based on dates. It produces the figures from the date nearest, but I need values from the next available date.

    Example: If ask for the values for th 2nd January, it returns 1st January even though I require the values for the 5th.

    I have tried ROUNDUP & CEILING but to no avail as the required dates are inconsistant.

    Any ideas?????

    User Comment Doug, 1-Feb-2008

    Importing csv data from a bank account into SAGE
    Have tried to import csv data exported from an internet bank account into SAGE only to find that field headings do not match those that SAGE requires.
    Any tips please?

    AddThis Social Bookmark Button