68 articles in this selection

2011/12/22

Interest calculations in Excel

2011/08/31

Excellent Analytics is a simple Excel plug-in that lets you import web analytics data from Google Analytics into a spreadsheet. It’s an open source project and 100% free to download and use for individuals and businesses.

2011/03/16

Today I have something completely different for you. It is only tangentially related to QlikView and lacks any practical application for QlikView professionals, but I know it will make you smile when you see it.

2011/03/10

Edgeseeker Add-in for Microsoft Excel gives you seamless accesses to historical market data from the most popular free internet sources, right from Excel. Pull price, dividend, fundamental and company data directly into an Excel spreadsheet in seconds.

2010/11/11

you’re in the market for an Excel consultant, you may have some questions. Or you may not even know enough to have a question. This document provides information about freelance Excel consulting and what you can expect if you contract with a consultant....

2010/09/27

I recently had the pleasure of attending a Microsoft Solutions for Business Intelligence seminar in Dublin. A large portion of the day was focused around Microsoft's new "In-Memory" B.I component PowerPivot. PowerPivot enhances the B.I capabilities of Microsoft Excel and was on more than one occasion referred to as "Excel on Steroids". It is being marketed as "Self Service BI" which has the potential to empower the end user to find the answer for themselves provided they can access the appropriate data sources. This sounds good in principle but without the content management facility provided by SharePoint it could intensify the silos of information that currently exist with Microsoft Excel leading to more versions of the truth. My initial reaction to the tool is that I am quite familiar with many facets of it from my Qlikview and Qliktech experience. It is testament to Qliktech and their growth that Microsoft should adopt such a similar methods....

2010/09/12

To be taken with a grain of salt: For years, BI vendors have promised a way for managers to easily build their own reports from scratch, without the help of IT staff. Now, with the release of Microsoft Office 2010, managers are finding they can do these tasks using a powerful new Excel feature, called PowerPivot. And, by its ease of availability if nothing else, this feature is promising to shake up the field of BI....

2010/08/20

The following code extracts the the pivot cache for every pivot table found in a workbook, puts it into a new pivot table and creates only one pivot field (to ensure that all rows from the pivot cache are incorporated in the total), and then fires ShowDetail, which creates a new sheet with all of the pivot table's data in. * Excel 2003: Only useful for tables with less than 65536 rows *...

2010/08/19

2010/08/03

QVExcel extends QlikView to empower users, such as Financial Analysts, to create bespoke reports (from financial reports to management accounts to budget reports) and perform quantative analysis that meets their organization's needs. This makes QlikView a more attractive offering to a wider audience....

2009/12/14

In-memory plugin for Excel 2003 and 2007.

2009/10/09

This macro will try to reduce the size of the Excel file by forcing Excel to recalculate the used range for each sheet.

2009/09/28

This article describes how to customize menus and menu bars in Microsoft Excel 2000 and later. This article contains step-by-step instructions and code samples to programmatically manage and customize menu bars, menus, commands, submenus, and shortcut menus in Microsoft Excel....

2009/09/25

This sub procedure creates a CommandBar and adds a few buttons and popups. It is only to demonstrate how to make it work. Both buttons with a caption, an icon and both are displayed.

2009/05/28

Gemini is designed to look as much like Excel as possible, so its data will look like a set of Excel tables. Anyone familiar with Excel 2007 tables (which is still probably only a small minority of the hundreds of millions of the world’s Excel users) will immediately feel at home. However, Gemini tables are actually stored as highly compressed, column-indexed, in-memory objects, not Excel worksheets. They are viewed and manipulated in the new Gemini client window, not in the Excel worksheet itself....

2009/04/23

2009/04/17

Excel Services maakt gebruik van de Microsoft Office SharePoint-technologie, waarmee u in de gehele organisatie op eenvoudige wijze Microsoft Office Excel 2007-werkmappen (.XSLX, .XSLB) kunt gebruiken, delen, beveiligen en beheren als interactieve rapporten....

2009/04/14

Watch a demo of self service analysis with Excel from the keynote presentation at the 2008 Microsoft BI conference in Seattle. Learn more about Microsoft BI at http://blogs.msdn.com/bi/

2009/03/23

Created in Excel, balanced scorecards enable you to monitor operations and tactics, while operational dashboards is a set of indicators regarding the state of a business metric or process—both features are in high demand for many large organizations. This book serves as the first guide to focus on combining the benefits of balanced scorecards, operational dashboards, performance managements, and data visualization and then implement them in Microsoft Excel....

2009/03/18

Even though I have few ideas on visualizing budget vs. actual performance, I thought this is a great way for You, my dear reader, to share your ideas. So what are you waiting for? Go ahead and tell us how you will visualize this data. The best visualization maker will get eternal glory and a copy of The Visual Display of Quantitative Information by Edward Tufte....

2009/03/18

Stacked bar(column) charts are a popular way to depict 2 more series of related data, like sales of 2 products. But there are several ways to stack the bars in a bar chart. Here is a list of 6 ways to stack them

2009/03/18

Almost 2 months ago our reader Jennifer sent me an email asking if there is an effective way to present market share changes between two periods for 2 products among five competitors. I have replied her promptly with whatever I could think of as better ways to present the data. But I also posted a visualization challenge: How to show market share changes?...

2009/03/11

I would like to generate a random value using a skewed normal distribution. For inputs I have the sample mean, standard deviation, and skewness. I would like to find a way to generate numbers randomly that would fit the skewed normal distribution described by the inputs....

2009/03/10

This plug-in adds a new Menu entry to the Office 2007 ribbon (specifically in Word, Excel, and PowerPoint). When clicked, your ribbon displays the classic Office 2003 menu, complete with the buttons and file menus you're used to from your old Office 2003 install. As Samer from FreewareGenius points out, not every single bit of functionality remains in the UBitMenu toolbar as is available in Office 2003—due mostly to changes in Office—but most of the features you're used to remain in all their glory....

2009/03/02

Simtools.xla and Formlist.xla are add-ins for Microsoft Excel (version 5 and later). Simtools adds statistical functions and procedures for doing Monte Carlo simulation and risk analysis in spreadsheets. Formlist is a simple auditing tool that adds procedures for displaying the formulas of any selected range. Also available here is Torndiag.xls, an Excel workbook for making "tornado" sensitivity-analysis diagrams. Simtools and Formlist were developed to facilitate applications of probability and statistics, and to help managers do complex decision analysis. These programs are distributed as freeware for individual use, and may be freely redistributed to students and faculty in an academic institution. All other rights are reserved....

2009/03/02

Our example of Monte Carlo simulation in Excel will be a simplified sales forecast model. Each step of the analysis will be described in detail. The Scenario: Company XYZ wants to know how profitable it will be to market their new gadget, realizing there are many uncertainties associated with market size, expenses, and revenue. The Method: Use a Monte Carlo Simulation to estimate profit and evaluate risk....

2009/02/20

2009/02/16

Treemapper is an Excel Add-In that allows an Excel user to quickly and easily generate a treemap visualization of any hierarchical data in Excel, or from a CSV or XML file. Treemapper is based on the Data Visualization Components also available from this group....

2009/02/15

The 'Clean' function removes non-printing characters from text strings in Excel - e.g. Clean(A1) should return the text in A1 with the alt-enters removed.

2009/01/22

Here is the problem: Build in VBA a routine that will calculate a "fuzzy match" between two text strings. This routine will allow us to say that one string is a 75% match to the other string.

2009/01/19

2009/01/07

If you want to use one of the built-in icons for your custom Outlook menu item or toolbar button, you'll need to know its FaceID. Here are all the FaceID's for the builtin Outlook 2000 icons. These ID's appear to be the same across different language versions of Outlook. Select the thumbnails to see larger tables....

2008/12/11

Collection of Excel and VBA tips.

2008/12/11

Classes are a powerful tool in intermediate to advanced level VBA programming. This page is an introduction to what a class and an object are and will hopefully get you started working with classes. This is by no means a comprehensive guide.

2008/12/11

I'm trying to create a list box on a sheet. I know there are numerous methods to do this, including the simple code below.

2008/12/10

Cynthia makes a mean cranberry-apple pie, and also a black bottom pie featuring a chocolate cookie crust, layers of chocolate and eggnog flavored pudding, and whipped cream. So you could accurately say that Peltier Loves Pie. But not pie charts.

2008/11/30

As a developer, you've probably, at some unfortunate point in your life (possibly several points, actually), been handed an Excel file that has been crammed full of "data" by someone in marketing and told to "do something with it."

2008/11/24

There is not much consistency among the Excel functions and VBA functions, and the various "standards" used in business. If you need to use week numbers, you need to understand how they work and what they represent.

2008/11/21

This hotfix fixes the following issue that was not previously documented in a Microsoft Knowledge Base article. You insert the ActiveX List box control in Microsoft Office Excel 2003. When you select the list, and you use the mouse wheel to scroll up or down, Excel 2003 crashes. And, you receive the following error message: Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience....

2008/11/20

List of tips to optimize slow VBA code.

2008/11/19

Sometimes you need to differentiate whether the user entered no text and pressed Ok, entered some text and pressed OK, or chose Cancel. This can't be done with just 'If MyString = "" ' because this will be true in two of the cases. There is an undocumented function to see whether any text has been entered into the InputBox. This is just a working example, you'll have to modify the three possible outcomes to your own needs....

2008/11/19

I am often asked by users 'what is the best way to distribute their macros?' My answer, is without doubt via an Excel Add-in. After all, this is what Add-ins are for. For those that are not sure what an Excel add-in is, it's is nothing more than an Excel Workbook that has been saved as an Add-in, File>Save as \ Microsoft Excel Add-in (*.xla). Once saved and re-opened the Workbook will be hidden and can only be seen in the "Project Explorer" via the Visual Basic Editor. It is NOT hidden in the same way as the Personal.xls as this can be seen (and made visible) via Windows>Unhide....

2008/10/14

We put together this wiki to create a training resource for data analysts using Excel. Together with your input, we'd like to develop a short list of the most important Excel skills for data analysis and gather teaching materials for each of these skills....

2008/10/14

2008/10/14

In this article I will demonstrate Excel's arithmetical facilities, the embedded rendering subsystems (there are two of them!) and the revolutionary approach which might just cause a paradigm shift. I hope you will discover that Excel effectively and efficiently incorporates practicality, tons of features, the multi-platform portability and the high performance with the unique and futuristic 3D engine features....

2008/10/14

Who said Excel takes lot of time / steps do something? Here is a list of 15 incredibly fun things you can do to your spreadsheets and each takes no more than 5 seconds to do.

2008/10/07

Dashboard Spy reader Andreas Lipphardt of microcharts.net has been working on some nice excel dashboards that monitor website traffic and use sparklines to provide analysis. He kindly documented some of his excel dashboarding processes and has provided you Dashboard Spy readers with the main content of this post....

2008/10/03

XLCubed delivers a powerful analysis, reporting and dashboard solution that unites rich OLAP capabilities centered in the familiar environment of Excel. Seems like a very nice tool.

2008/10/03

How to create an OLAP cube for use with Excel Pivot Tables.

2008/10/03

This is by no means an all-inclusive list of Excel add-ons. If it were, the list would probably be in the thousands. Here is Support Analytics’ short list of those we like and find very useful to data analysis and visualization in no particular order....

2008/09/27

Dashboard reports allow managers to step back from the details and see the key trends and relationships that drive their companies. Excel is an excellent tool for creating these reports.

2008/09/27

New owner of Bonavista Microcharts.

2008/09/19

Guy selling Excel dashboarding stuff. Nice, but already know how to do all these things myself.

2008/09/18

2008/09/18

Pointy Haired Dilbert is a site about Excel Tips, Technology Tidbits, Business Insights.

2008/09/18

In this post, we are going to review 7 of the MS Excel Micro-charting Alternatives so that you have a good idea of finding the right micro charting tool for your purpose.

2008/09/15

Create rich reports and Excel dashboards with more information per square inch. MicroCharts, winner of the BI Network’s 2006 Data Visualization Competition, enhances Excel, OLAP and standard reporting with powerful, compact visualizations such as sparklines, bullet graphs and other in-cell charts....

2008/09/15

What do C-level executives look for in an executive dashboard? I believe it is a concise representation of whatever business scenario the executive wishes to examine. The dashboard design problem is, of course, how to represent a lot of information without cluttering the user interface. A “hot” design solution right now in general is the idea of “sparklines”. In particular, we are seeing sparklines appear in executive dashboards more and more often. Today we examine one dashboard in detail....

2008/09/15

Select a blog from the list on the left, and the recent headlines appear here. Click a headline to view the article at the original site. A few of the feeds are not blogs per se, but you might find them useful.

2008/09/15

2008/09/15

I discovered a while ago that you can create a Shape from a user-defined function. This opens the possibility for having custom made graphics dependent on other cells. Meaning, when the data changes, your graphic changes too.

2008/09/14

Bullet graphs provide an effective way to dashboard target vs. actual performance data, the bread and butter of corporate analytics. Howmuchever effective they are, the sad truth is there is no one easy way to do them in excel. I have prepared a short tutorial that can make you a dashboard ninja without writing extensive formulas or installing unknown add-ins. So get out your shinobigatana and join me in a fresh excel sheet arena....

2008/09/14

A set of free User Defined Functions for Excel to create Sparklines : the simple, intense, word-sized graphics invented by Edward Tufte.

2008/08/27

This post presents a VBA procedure that extracts all data from a chart, and places it into a new worksheet. Why would you want to extract a chart’s data? Sometimes a chart gets its data from diverse sources, and you’d like to have the data in one place. You may receive a workbook that contains a chart, but the chart’s data is in a workbook which you don’t have access to. For whatever reason, this procedure comes in handy....

2008/08/20

Dashboard: Examples & Best Practices. From Excel Dashboards to Enterprise Business Intelligence, these dashboard implementations contain KPIs, metrics, charts, trends and more.

2008/08/13

List of Excel Tips and Techniques

2008/08/06

Tufte meets Excel (and other information visualization musings)

2008/08/06

I’ve put together an extensive web site (http://peltiertech.com), which has an emphasis on working with Excel charts. Many of the tutorials and examples are techniques for achieving charting effects that, at first glance, seem impossible. I am active in Microsoft’s newsgroups and in other online forums, such as Mr. Excel. I can be reached at jonxlmvp@peltiertech.com....