Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
The Excel Team Blog » All Tags » Power Tips (RSS)

Browse by Tags

  • Where to Find the Form Controls

    Today’s author is Jan Karel Pieterse, an Excel MVP. In this article, he shows the different locations of the form controls in Excel. You can find additional details on how to use these controls on his web site: http://www.jkp-ads.com/articles/controls.asp . Excel 2007 and 2010 In Excel 2007 and 2010, the form controls and control toolbox controls are slightly hidden. First of all, you need to show the Developer tab in the ribbon. Here is how that's done: In Excel 2007, click the Microsoft Office...
    Filed under:

  • Report statistics about a Set of Workbooks

    Today’s contributor is Sarah Hothersall, Liaison Director at Lyquidity. She’s providing information about the Workbook Statistics add-in, a free tool for Excel users that was created by Sandy Marshall, a Product Manager at Lyquidity, who is in charge of development of ComplyXL for enterprise spreadsheet management. To download this add-in, visit http://www.lyquidity.com/workbookstatistics . Sometimes it is helpful to report statistics about a set of workbooks contained in a folder. You can use this...
    Filed under:

  • Preventing auto_open and Workbook_Open events from running

    Today’s author is Jan Karel Pieterse, an Excel MVP. You can find more useful tips from Jan Karel on his website: http://www.jkp-ads.com/ . This post informs you about Name Manager, a free utility that helps you manage defined names in your workbooks. As a fulltime developer I oftentimes open files containing VBA and want to be able to run code. At the same time I sometimes want to prevent Workbook_Open event code or an Auto_Open macro from running. This little article shows you how to achieve that...
    Filed under:

  • Daniel’s Extreme Lookup Collection

    Today’s author is Daniel Wiesenfeld, an Excel and Access Power User who is sharing his Extreme Lookup Collection with us so we can use the Excel User Defined Functions (UDFs) he created to enhance the lookup functionality. His web site daanalytics.com is currently under construction and should be available soon. In the Visual Basic Editor, insert a Module and paste the following code: ' XVLOOKUP (& XHLOOKUP) ' Works just like a vlookup (and hlookup) except that the user refers to a lookup...
    Filed under:

  • Create conditional drop-down lists

    Today’s author is Reinout Dorreboom from the Netherlands, a Technical Consultant at Getronics, where he has worked with Office applications for many years, and where his Microsoft Certified Training skills enabled him to help other people get up to speed with Excel. In Excel 2007 (and earlier), it’s possible to create a drop-down list. By using the INDIRECT function, you can then create additional drop-down lists that are conditional to the first drop-down list. In this example we’ll create the following...
    Filed under:

  • Easily manage defined names in Microsoft Excel

    Today’s author is Jan Karel Pieterse, an Excel MVP. You can find more useful tips from Jan Karel on his website: http://www.jkp-ads.com/ . This post informs you about Name Manager, a free utility that helps you manage defined names in your workbooks. If you need a free utility to manage defined names in your workbooks, this one is a must-have. List all names in your active workbook. Filter them using 13 filters including "With external references," "With errors," "Hidden...
    Filed under:

  • Find Combinations

    Today’s author is Bill Seddon from Lyquidity Solutions, who informs us about a free tool for Excel users that allows you to find combinations of numbers that total to a selected value. For more information and a video, see http://www.lyquidity.com/findcombinations . Imagine you are an auditor or cash accountant and need to reconcile cash balances to their invoices.  Which combination of values makes up each cash value?  Not surprisingly, the Find Combinations add-in has been popular with...
    Filed under:

  • Using form controls on a worksheet

    Today’s author is Rob van Gelder, previously quite active in the online Excel scene (Newsgroups, Excel forums, his own but currently inactive website) and who just upgraded to Excel 2007 because he still likes to dabble. The following power tip shows that he’s still having fun with form controls. My goal is to use form controls on my sheet. · One up/down button to change the Edit Year · One slider to change the Quantity · One slider to change the Price I don't need a slider for Total Value, because...
    Filed under:

  • Adding Every Other Cell in a Column

    Today’s author is Bill Jelen, author of 24 books on Microsoft Office Excel. He is a regular guest on TechTV with Leo Laporte and the host of MrExcel.com , which includes more than 300,000 questions and answers about Excel. Jenny asks "Is there a quick way of adding every other cell ?, I have a column of about 250 cells, and I have to select them manually using Ctrl. Can you help ?" Jenny, this is a matter of using a little math to remember how to determine if a number is even or is odd...
    Filed under:

  • Calculate the ISO Start of Year date

    Today’s author is Shane Devenshire, who has been an Excel MVP for many years and who regularly contributes to Excel Newsgroups. He has published over 300 articles in different computer magazines, and co-authored numerous books. Excel might be better today because of the many bugs he posted when he was a Beta tester! This post shows you how to use a formula or custom VBA function to calculate the ISO Start of Year date. Here is a formula for calculating the ISO Start of Year date for any legal Excel...

  • Copying Worksheets with a List or Table

    Today’s author is Ron de Bruin, an Excel MVP . You can find more useful tips from Ron and links to Excel add-ins at his website: http://www.rondebruin.nl/ Problem : It is not possible to copy more than one sheet to a new workbook or existing workbook if there is a List (Excel 2003) or a Table (Excel 2007) in one of the worksheets. How to reproduce: In a new workbook with two or more sheets, add a table or a list in one of the worksheets. Select more than one sheet with the CTRL key held down. (Be...

  • Remove Zeros and Blanks from a List of Values

    Today’s author is Bob Umlas, Excel MVP and author of This isn’t Excel, it’s Magic, a book that is filled with tips and tricks for getting the most out of Excel. This post shows you how to quickly remove zero values and blank cells from a list that contains values. Here’s a formula which enables you to access a list of values interspersed with zeros or blanks and it will pick up only the non-zero values in the same sequence they’re listed. It’s better to illustrate. Suppose you have this list in A1...

  • Creating a Custom Popup Menu

    Today’s author is Derek Mang, owner of Systems Solution Developers Inc. You can find more useful tips from Derek on his website: www.officevbasolutions.com . This post shows you how to create a custom popup menu in Excel. Microsoft Excel has lots of popup menus that are made available to the user with a right-click depending on what your doing - hence the term context menu. Right-click on a cell and you get the Excel "cell" popup menu and its available choices. This menu is subject to customization...

  • Submit your Power Tips

    The Power Tips feature of the Excel Team Blog has started with useful articles from MVPs Ron de Bruin and Jan Karel Pieterse. Thank you both! For everyone else who is interested in contributing to this blog, here’s a quick reminder how the feature works. What’s a Power Tip? A Power Tip can be a code sample, or some other Excel trick that you have learned along the way, and that you think might be useful to others. They can be old favorites or new discoveries. Who can submit Power Tips? Anyone! Can...

  • Add Buttons to the Quick Access Toolbar and Customize Button Images

    Today’s author is Ron de Bruin, an Excel MVP . You can find more useful tips from Ron and links to Excel add-ins at his website: http://www.rondebruin.nl/ This post shows you how to add a button to the Quick Access Toolbar (QAT) for one or all workbooks. It also shows an example of how you can change the image of a QAT button. One reason you might want to change a button’s image is that a lot of commands use the same button image (a green circle). For more information about adding missing built-in...
    Filed under: ,
1 2 Next >
Copyright Excel User Group and the relevant contributors, 2009. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.