Table Formula in Excel (Something I didn’t Know Till Yesterday)

Table formulas were something that I discovered recently. Actually our reader m-b commented that he prefers to convert a range to a table and then employ table formulas instead of named ranges. That got me curious enough to explore them further and here's what I learnt. A Table in Excel A table is a feature in Excel that makes it easier to format and analyze a set of data points in a spreadsheet. Tables were introduced ...

Continue Reading

Table Formula in Excel (Something I didn't Know Till Yesterday)

Named Range – A Few Quickies

Posted on 21 January 2010

Named Range - A Few Quickies
Named Ranges are probably one of the most useful features in Excel. Named ranges can add interactivity, make long formulas shorter and and if used properly, generally provide a clean mechanism to share information across the workbook. I remember being mighty impressed with Peter Rakos 3D rotation model last year and spent a good amount of time trying to understand it. The VBA code is only a few lines with the major work being taken up by the ...
Continue Reading

Comments (11)

So How Many Of These Excel Shortcuts Do You Know, Punk ?

Posted on 19 January 2010

So How Many Of These Excel Shortcuts Do You Know, Punk ?
So you thought you knew every goddam' Excel shortcut out there? Thought that you were the quickest draw around the office block. Not so fast sunny boy ... not so fast ! You've just ventured into the mean and vicious badlands of the Bison. You have two choices - stop reading this here ... ... OR ... stay back and take up the challenge .... So before I have your ego blown off, I will give you 7 chances to salvage it. Excel Shortcut ...
Continue Reading

Comments (23)

An Excel Chart’s Journey Across Versions

Posted on 10 January 2010

An Excel Chart's Journey Across Versions
Sometime back I wrote a post on making control charts using Excel. The chart was initially created in Excel 2003. The chart consisted of the plot values, the average line and upper & lower control limits. While the main data series was a line chart, the average, upper and lower control limits were plotted as dots of an X-Y and then were extended into lines using the horizontal error bars. Shown below is a sample. ...
Continue Reading

Comments (2)

Data Validation List in Excel – Create Drop Down List Using Data Validation in Excel

Posted on 25 December 2009

Data Validation List in Excel - Create Drop Down List Using Data Validation in Excel
Data Validation feature in Excel allows the user to create a drop down list in Excel. The drop down list created using data validation allows the user to pick and choose a single value from the entire list and thus prevents entry of invalid values. The drop down list can be created by typing in a set of values, using a range of cells or by writing a formula in the data validation option box. Create a Drop Down List ...
Continue Reading

Comments (5)

Custom Format to Catch Numbers Appearing as Text

Posted on 11 December 2009

Custom Format to Catch Numbers Appearing as Text
When raw data (in text *txt or csv format) is imported to excel, the conversion can sometimes have an unintended side-effect - the numbers come out as text after the process. The only choice left is to identify the columns that contain numbers masquerading as text and then to use the text to column option on each of the columns one-by-one. If the number of columns is large, eyeballing the data can be a bit of a strain. Here a ...
Continue Reading

Comments (3)

Keyboard Shortcuts in Excel to Sort, Add, Hide Pivot Table Items and Fields

Posted on 03 December 2009

Keyboard Shortcuts in Excel to Sort, Add, Hide Pivot Table Items and Fields
Here are some quick keyboard shortcuts in Excel to help you survive in the pivot table jungle. Keyboard shortcut to Sort Fields and Items in a Pivot Table Thought that you would have to drag the field or the item across the pivot table using the mouse. Hold on - you can use the keyboard too. Simply type the field or the item ...
Continue Reading

Comments (5)

Data Validation in Excel – How to Use Data Validation Feature in Excel

Posted on 24 November 2009

Data Validation in Excel - How to Use Data Validation Feature in Excel
Data Validation feature in Excel prevents invalid entries from being entered into a cell in a sheet. Data validation can be set up to work with numbers, text string, date / time or customized formula so that we can restrict the set of values that can be entered in a cell and also prevent wrong entries being made at the time of data entry. Data validation can also be used to create drop down lists so that the user ...
Continue Reading

Comments (1)

Excel 2010 vs Excel 2007 : Basic Performance Test

Posted on 24 November 2009

Excel 2010 vs Excel 2007 : Basic Performance Test
My first impression of Excel 2010 was that it 'felt' faster than Excel 2007. So one of the first things that I did was to take it for a test ride. Now, I must admit, testing isn't my forte. A long long time back when I was a developer, I used to write horrible code. I had a fancy for declaring variables like i1, i11, i111 (and so on and so forth) and seldom wrote comments that could explain ...
Continue Reading

Comments (5)

Slicer in Excel – The Pivot Table Remote Control

Posted on 20 November 2009

Slicer in Excel - The Pivot Table Remote Control
Slicer What is a Slicer? One can think of a slicer as an extension of a pivot table which makes the job of filtering the pivot table data easier. One shortcoming that pivot tables earlier had was that you could not control them remotely. Short of VBA, you could do things with pivot tables only when you had one in front of you. A slicer on the other hand acts as a 'remote control' for the pivot table. You can ...
Continue Reading

Comments (7)

Sparklines in Excel – New Features in Excel 2010 Series

Posted on 19 November 2009

Sparklines in Excel - New Features in Excel 2010 Series
Excel 2010 introduces a new feature called sparklines. Sparklines are essentially miniature charts showing only the plot and pretty much nothing else (though you can turn on chart axis if you want). The advantages are obvious. In the same amount of space that it to present a normal excel chart with a handful of data points, you can pack a dozen or more sparkline charts. If used intelligently, the data loss due to absence of reference lines and ...
Continue Reading

Comments (6)

Excel 2010 – Beta Review

Posted on 18 November 2009

Excel 2010 - Beta Review
Excel 2010 beta is out today. For those who read my review of Excel 2007, I mentioned that the chances of my being able to try out Excel 2010 anytime soon weren’t very high. Excel 2010, I thought at that time, and considering how Excel 2007 turned out, would simply be too big a resource hog to work with the hardware that my pc runs on. In that case I would have to give my faithful old pc ...
Continue Reading

Comments (4)

Pivot Table Quick Tip – Using Pivot Table to Separate Data

Posted on 16 November 2009

Pivot Table Quick Tip - Using Pivot Table to Separate Data
So the poor little manager looks at his computer and says, "how to heck am I supposed to separate this data down to individual sheets for each department." The manager in question had just been handed over a large file containing financial information for all the departments in his organization and was asked to break it down so that each department's data should end up in a separate sheet. The manager ...
Continue Reading

Comments (4)

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     

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)

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)

Custom Format in Excel – How to Format Date and Time

Posted on 07 September 2009

Custom Format in Excel - How to Format Date and Time
In custom format in Excel we discussed using the custom format feature to format text and numbers. In this article, we format date and time values and see how we can make them appear more meaningful. If you haven't had a chance to read through the first part where we talked about basics of the custom format feature, it is highly recommended that you do so before coming back and finishing this one off. Before we begin, again please bear ...
Continue Reading

Comments (2)

Custom Format in Excel – How to Format Numbers and Text

Posted on 27 August 2009

Custom Format in Excel - How to Format Numbers and Text
The custom format feature in Excel allows the user to “paint” or mask a number, text, date or time value in a user defined custom format. Before we get into the details, it is important to remember that when you format a cell using this feature, it does not change the value present in the cell. The only change is of how the value appears to the user on the screen. A custom format can be applied to a particular cell ...
Continue Reading

Comments (18)

Excel Color Palette and Color Index change using VBA

Posted on 20 August 2009

Excel Color Palette and Color Index change using VBA
Excel Color Palette has an index of 56 colors which can be modified using VBA. Each color in the palette is associated with a unique value in the index that can be changed programatically. At times it is useful to know the relative positioning of the various colors within this index as well as how various versions of Excel treat colors. This is the first in the series of articles that will expore this further. How to generate Excel Color Index ...
Continue Reading

Comments (19)

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