OLAP Cube in Excel and Pivot Table From External Data

OLAP Cubes provide a convenient way to crunch data in Excel. Imagine a Pivot Table pulling information from thousands or millions of data points with hundreds of columns and you being able to share this humongous data set with colleagues using an Excel spreadsheet that weights nothing more than a few Kilobytes. Add to this the ability to update the source data while the users get to look at that information real time. That’s what ...

Continue Reading

OLAP Cube in Excel and Pivot Table From External Data

Pivot Table Quick Tip – Group By Year, Quarter, Month and Date Field

Posted on 02 November 2009

Pivot Table Quick Tip - Group By Year, Quarter, Month and Date Field
It's monday morning and you've got a pivot table on your hands - its source data consisting of three columns – product, date and sales volume over a preiod of time. A pivot table created using this as underlying data may typically have the date running across rows, the product category as the column or page field and finally the sum of sales appearing in the data field in ...
Continue Reading

Comments (11)

Halloween Special – 3 Wicked Things You Can Do with Excel

Posted on 30 October 2009

Halloween Special - 3 Wicked Things You Can Do with Excel
I reckon that today would be the last working day before Halloween and it might be your only chance to get even. So here are a few Excel tricks especially for you this Halloween - each one getting progressively meaner. Send somebody a Workbook that just doesn't Scroll the Right Way This one is for your super boss (I mean boss's boss). Now wish as you may, you really can't do too many bad things to him, right - so this one's ...
Continue Reading

Comments (3)

Scatter Chart with Highlight Box to Group Data Points in Chart

Posted on 28 October 2009

Scatter Chart with Highlight Box to Group Data Points in Chart
A scatter chart helps to plot data with two or more variables. One great advantage that a scatter chart has is the ability to show a relatively large number of data points in a small area. However, as the number of points plotted within the same space increases, it sometimes becomes difficult to distinguish individual data sub-sets. One quick solution to group all the points within the desired data sub-set is ...
Continue Reading

Comments (1)

Chart Label Trick – Using Interactive Labels on Chart

Posted on 25 October 2009

Chart Label Trick - Using Interactive Labels on Chart
In Applying Offset Labels to Chart we looked at how a chart's labels can be offset to avoid cluttering. But what if you wanted to use the chart to narrate a story - and a pretty long one at that? How would you do it? If you turn on the chart axis labels, the entire thing gets messed up. Roots growing out of the chart axis wasn't exactly the point that we would like to highlight to someone who's ...
Continue Reading

Comments (4)

Stock Tracking Dashboard and Live Stock Price Quotes Using Excel

Posted on 22 October 2009

Stock Tracking Dashboard and Live Stock Price Quotes Using Excel
Nearly everybody I know uses Excel for tracking the returns in the stock market. Not many however know that Excel can be used for online stock tracking and getting live price information from stock exchanges like NYSE and Nasdaq as well. Here's how. Establishing a connection to the MSN server for Stock Quotes Click on the data tab in the Excel ribbon (in Excel 2007 and later). In Excel 2003 and earlier, ...
Continue Reading

Comments (10)

Change in Position – Which Chart Should I Use?

Posted on 16 October 2009

Change in Position - Which Chart Should I Use?
As an analyst, I often need to keep track of the change in position of various players in the industry. More often than not, the position change needs to be tracked between two specific points in time - say quarter 1 vs. quarter 2 or perhaps year 1 vs. year 2. In this article we take a look at a few alternative approaches to plotting position changes and find out which one serves us best. For this example let's take ...
Continue Reading

Comments (11)

Barcode Font in Excel and other Trivia

Posted on 09 October 2009

Barcode Font in Excel and other Trivia
Yesterday's Google doodle was a barcode and that got me curious. So here are some free barcode fonts in Excel and a few interesting tidbits about them. Barcode History Barcodes were developed in 1948 by Bernard Silver and were patented in 1952. While the linear pattern of the barcode is prevalent today, during its inception, bulls-eye patterns were also experimented with. However due to the problems encountered while printing of the later, the liner version caught on. The first ever purchase using ...
Continue Reading

Comments     

Drop Down List in Excel Made Easy

Posted on 08 October 2009

Drop Down List in Excel Made Easy
A drop down list in Excel allows users to pick a value from a restricted set. By providing user a choice and yet preventing impermissible values, drop down lists help make your spreadsheets interactive and foolproof. While there are more than 1 way to make them, the most commonly used method is using data validation. You can create both static and dynamic drop down lists in Excel - the later re-sizing themselves as newer values are added or existing ones ...
Continue Reading

Comments     

Chart Label Trick – Label Last Point in a Line Chart and Offset Axis Crossover

Posted on 07 October 2009

Chart Label Trick - Label Last Point in a Line Chart and Offset Axis Crossover
Kaiser (Junkcharts) posted this line chart sometime back. A recreated chart is shown below where labels have been added to last point of each series in the chart. If you noticed, the labels have also been enhanced to show both the series name and the value of the data point. Lastly, the Y Axis of the chart has been offsetted to crossover at the last category. The benefits are obvious - the ...
Continue Reading

Comments (1)

Probability Charts – The IMF Way

Posted on 02 October 2009

Probability Charts - The IMF Way
IMF's world economic outlook report bought forth some interesting charts. The charts in question depicted the probability associated with future data points and highlighting the zones of uncertainty associated with each estimate. You can access the entire report here. Creating a Chart with Probability Bands around the chart data Often times there is a bit of uncertainty associated with data points. "Confidence level" is a term often used by statisticians to ...
Continue Reading

Comments (1)

Butterfly Chart – Excel Chart with Dual Converging Scales

Posted on 01 October 2009

Butterfly Chart - Excel Chart with Dual Converging Scales
A Butterfly chart is a chart where two entities are compared side by side using scales meeting at the center. Due to its shape, the chart resembles a butterfly and hence the name. These charts are sometimes also known as Funnel or Tornado Charts though I find "butterfly" to be a better description as it allows for a greater variation in shape than a funnel or a tornado does ! So let's jump ...
Continue Reading

Comments (8)

Using Custom Format to Color Chart Axis and Chart Labels

Posted on 30 September 2009

Using Custom Format to Color Chart Axis and Chart Labels
Custom Format can be used to format numbers, text, dates and time. The technique can be extended to charts as well where the custom format feature can be used to color the chart axis and chart labels. In this example, let's say we wanted to show the sales numbers for a particular duration as a chart. We would, in addition, like to have some kind of separation between what is considered below, average and above average performance. Custom format ...
Continue Reading

Comments     

Chart Label Trick : Applying Offset Labels to Excel Chart Series

Posted on 24 September 2009

Chart Label Trick : Applying Offset Labels to Excel Chart Series
In Chart Label Trick - Descriptive Labels we talked about adding custom labels to chart. In this article we discuss offsetting chart labels - which means positioning the labels above or below the actual data points at a predetermined distance. Getting the chart data in place Like always, we first get the chart data in place. For the purpose of this chart, we will use two data series - the first ...
Continue Reading

Comments (8)

HTML to Excel using Javascript and Excel to HTML using VBA

Posted on 23 September 2009

HTML to Excel using Javascript and Excel to HTML using VBA
This two part article consists of: Part A - Converting HTML to Excel using Javascript Part B - Exporting Excel to HTML using VBA HTML to Excel using javascript Let’s first take a look at tables in html. Data tables are typically stored in HTML using the <table> tag. Such a table will typically consist of one or more rows (<tr> tag) with each row having one or more cells (<td> tag). To read a table from an HTML document ...
Continue Reading

Comments (12)

VBA Code – Multiple Column Text to Column Conversion

Posted on 21 September 2009

VBA Code - Multiple Column Text to Column Conversion
Here's a bit of VBA code to convert Text to Column which will work across multiple columns selected together at one go. ...
Continue Reading

Comments (1)

Chart Label Trick – Converting Chart Axis Labels to Table

Posted on 18 September 2009

Chart Label Trick - Converting Chart Axis Labels to Table
The labels used with the chart category axis are probably the most ignored part of the entire chart making process. You see, the labels on the value axis can be modified, the bars, the lines and the plot area - all of them can be embellished - colors added, widths adjusted, descriptions applied - the works you know ! Even the poor little gridlines can afford a makeover. But what about the chart's category axis label - what do ...
Continue Reading

Comments (3)

Conditional Formatting and Custom Format – Use More than Three Colors

Posted on 16 September 2009

Conditional Formatting and Custom Format  - Use More than Three Colors
Conditional formatting (in pre Excel 2007 versions) had a limitation of only allowing for upto three conditions for formatting cells. A quick way to include more than three conditions when using conditional formatting is to use the custom format feature in tandem with it. Conditional formatting can be used to specify the first three conditions and then upto three additional conditions can be specified using custom format - giving us 6 different colors to work with. ...
Continue Reading

Comments (1)

Chart Table – When Chart meets Table

Posted on 15 September 2009

Chart Table - When Chart meets Table
Chart table - as the name suggests - is a beautiful fusion of a chart and a table giving rise to a visually appealing yet information rich presentation of data. Not that the concept itself is anything new - experts have openly and vociferously championed the cause of the chart table, its various facets have been discussed at length in numerous forums and the latest versions of excel come equipped with tools that further aid their creation (namely sparklines). However ...
Continue Reading

Comments (6)

Custom Format to Left Align Text Symbol and Right Align Numbers

Posted on 13 September 2009

Custom Format to Left Align Text Symbol and Right Align Numbers
In Custom Format Numbers and Text we discussed using the custom format feature on numbers and text. Here is a neat little trick using which we can format a cell to make the text symbol align left and the numbers present in the cell right. Hold your breath..here comes... "Custom Text Symbol"* General where: Custom Text Symbol is the customized symbol that you would like to appear against each number. So if ...
Continue Reading

Comments (3)

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES