DATE Formula Excel – How to use Excel DATE Function

Syntax of DATE Formula Example of DATE Formula Possible Errors returned by the DATE Formula DATE formula in Excel returns the number representing the date for a given day, month and year. The DATE formula is typically used to construct a date by providing the constituting parts such as day, month and year. The minimum and maximum values that a date can take is Office 2003 is January 1, 1900 and December 31, 9999 ...

Continue Reading

DATE Formula Excel - How to use Excel DATE Function

VBA Chart

Posted on 20 July 2009

VBA Chart
Making a chart using VBA can be easy. VBA provides a handle to a chart object using the "ChartObject" class and to the chart data series trough the "Series" class. Let's look at a basic example where we use VBA to create an empty chart to our spreadsheet and then add data to it. [cc lang="vb"] Sub CreateChart() Dim ChartObj As ChartObject Dim ChartSeries as Series Set ChartObj = ActiveSheet.ChartObjects.Add ( Left: = 100, Width: = 550, Top: = 75, Height: = 325) Set ...
Continue Reading

Comments (7)

Pivot Table in Excel – How to Create and Use Pivot Table

Posted on 15 July 2009

Pivot Table in Excel - How to Create and Use Pivot Table
Pivot Table Basics Learn How to Create Pivot Table in Excel Things that can be done with Pivot Tables in Excel Pivot Table Download What is a Pivot Table? A pivot table is a table that stores the summary of an underlying data set in a condensed form. A pivot table consists of rows, columns, page and data fields each of which can be moved around interactively and help the user isolate, expand, group and sum the data in real ...
Continue Reading

Comments (8)

Make Mouse Scroll Wheel Work in VBA Editor (IDE)

Posted on 14 July 2009

Make Mouse Scroll Wheel Work in VBA Editor (IDE)
This ranks right at the top of annoyances when it comes to working with the VBA IDE - the mouse scroll wheel simply does not work. So if you have more lines of code than can fit into a single screen in the VBA IDE, you end up having to drag the scroll bar to up/down to navigate. Jimmy Peña recently posted this very useful VBA tip on his informative blog that provides the solution to this issue. ...
Continue Reading

Comments (12)

MATCH Formula Excel – How to use Excel MATCH Function

Posted on 13 July 2009

MATCH Formula Excel - How to use Excel MATCH Function
Syntax of MATCH Formula Example of MATCH Formula Possible Errors returned by the MATCH Formula MATCH formula in Excel returns the relative position of a value in an array or a range MATCH Formula Syntax MATCH Formula has three parts: MATCH (value_to_find, range_to_find_value_in, match_type) value_to_find value_to_find is the value which we are trying to find. It can be a number or a string. If the value_to_find is specified as a ...
Continue Reading

Comments (5)

How to hide a sheet in Excel

Posted on 12 July 2009

How to hide a sheet in Excel
How to Hide a sheet from users temporarily ? This option works only if you want Excel to hide the sheet temporarily - subsequent users can always make the worksheet visible again anytime they wish to. To make a worksheet invisible, simply click on the ‘Format’ -> ‘Sheet’ ->’Hide’ option from the menu and it will hide the sheet from view. This is oftentimes the simplest option. How To Hide a sheet with restricted ...
Continue Reading

Comments     

ADDRESS Formula Excel – How to use Excel ADDRESS Function

Posted on 10 July 2009

ADDRESS Formula Excel - How to use Excel ADDRESS Function
Syntax of ADDRESS Formula Example of ADDRESS Formula Possible Errors returned by the ADDRESS Formula ADDRESS formula in Excel generates the address for a cell when provided a row and a column number. ADDRESS Formula Syntax ADDRESS Formula has five parts: ADDRESS (row_num, col_num, ref_type, ref_style, sheet_name) row_num row_num is the row number in which the cell for which you are trying to generate the address is located. It is ...
Continue Reading

Comments (6)

Array Formulas Examples

Posted on 08 July 2009

Array Formulas Examples
Array formulas help you turn normal Excel Formulas into super formulas. Here are a few examples of Array formulas. These examples highlight the use of multiple conditions within a single array formula and how we can club multiple formulas in to a single array formula. Array formulas are powerful - no doubt about that. However before we go any further, one must look at the flip side to using array formulas. They take much more computational power and can slow ...
Continue Reading

Comments     

Javascript Charting Library

Posted on 06 July 2009

Javascript Charting Library
A Javascript charting library is a compilation of javascript code which helps you create charts in a browser. Here's a list of 5 Javascript charting libraries that I had a chance to review : (Each of the Javascript charting libraries reviewed below would offer its own custom functions to interact with the chart object. If a chart fits your need, you may want to read the chart library's API documentation in greater detail. However, if you're short on development time, this ...
Continue Reading

Comments (1)

Array Formulas in Excel – Excel Array Formula Syntax & Array Constants

Posted on 05 July 2009

Array Formulas in Excel - Excel Array Formula Syntax & Array Constants
Array Formulas in Excel An Array Formula in excel is a formula that uses arrays instead of single cell value as input. Excel Array Formulas can be thought of as many formulas packed into a single super formula. In this article we will take a look at the syntax of an array formula, its various parts, learn how to write a basic array formula and then graduate on to writing the more powerful versions of it. Before we begin, let ...
Continue Reading

Comments (21)

COUNTIF Formula Excel – How to use Excel COUNTIF Function

Posted on 04 July 2009

COUNTIF Formula Excel - How to use Excel COUNTIF Function
Syntax of COUNTIF Formula Example of COUNTIF Formula Possible Errors returned by the COUNTIF Formula COUNTIF formula in Excel counts the numbers of cells in a range that meet the given condition. COUNTIF Formula Syntax COUNTIF Formula has two parts: COUNTIF (range, criteria) range range is the range in which the value that you are trying to find out is located. criteria criteria is the value whose occurence in the above range we would like to count. ...
Continue Reading

Comments (4)

IF Formula Excel – How to use Excel IF Function

Posted on 04 July 2009

IF Formula Excel - How to use Excel IF Function
IF formula in Excel checks whether a given condition evaluates TRUE or FALSE. Syntax of IF Formula Example of IF Formula Possible Errors returned by the IF Formula IF Formula Syntax IF Formula has three parts: IF(criteria, value_when_true, value_when_false) criteria criteria is the condition that we would like to evaluate for being TRUE or FALSE. value_when_true value_when_true is the value that will be returned by the function when the above criteria evaluates to TRUE. This ...
Continue Reading

Comments (12)

Javascript Get the Current Path without %20 and / backslash

Posted on 03 July 2009

Javascript Get the Current Path without %20 and / backslash
To get the current path using javascript without the %20 and / (backslash), use the following code: [cc lang="vb"] var path = document.location.pathname; alert (path); [/cc] However using the javascript path variable in this manner gives something like this: So now we use the string replace function in Javascript to rectify this: [cc lang="vb"] var path = document.location.pathname; path = path.replace(/\//gi, ""); path = path.replace(/%20/gi, " "); path = path.replace(/\\/gi, "\/"); alert (path); var path = document.location.pathname; [/cc] This javascript code gives us the path ...
Continue Reading

Comments (3)

How to Choose a Chart

Posted on 03 July 2009

How to Choose a Chart
How to choose a chart? Which chart would best fit the data set that I have? How do I choose the better chart type from Excel? Finding the right type of chart for the data set that you have can be a bit of a challenge. Given a set of seemingly equivalent alternatives, picking one chart over the other can turn out to be quite an interesting exercise. The chart chooser diagram can help you make a choice in situations ...
Continue Reading

Comments (8)

VLOOKUP Formula Excel – How to use Excel VLOOKUP Function

Posted on 01 July 2009

VLOOKUP Formula Excel - How to use Excel VLOOKUP Function
Syntax of VLOOKUP Formula Example of VLOOKUP Formula Possible Errors returned by the VLOOKUP Formula VLOOKUP formula matches a string against the 1st column of a range and returns any cell value from the matched row. VLOOKUP Formula Syntax VLOOKUP Formula has three parts: VLOOKUP (value_to_find, range_to_search_in, column_number_to_return, match_type) value_to_find value_to_find is the value that we would like to find. You can specify a string, a number or a cell ...
Continue Reading

Comments (21)

VBA For Loop – For Next and For Each In Next

Posted on 01 July 2009

VBA For Loop - For Next and For Each In Next
The For Loop in VBA is one of the most frequently used loops in VBA. The For loop has two forms: For Next and For Each In Next. The For loop is typically used to move sequentially through a list of items or numbers. To end the for loop at any given point we can use the exit for statement. Let's take a closer look at each of these loops. ...
Continue Reading

Comments (28)

Chartjunk

Posted on 29 June 2009

Chartjunk
Chartjunk, a term first coined by Edward Tufte, refers to all those elements in a chart that add no further value. Chartjunk comes in many shapes and size - redundant labels, unnecessary gridlines, over-the-top use of colors and everything that a 'creative' mind can think of. However what should be kept in mind is that this term also has a strong contextual meaning. An element that may appear as chartjunk in one graph may serve a useful function in another ...
Continue Reading

Comments (6)

How To Create Pivot Table Using VBA

Posted on 28 June 2009

How To Create Pivot Table Using VBA
How to create a pivot table using VBA? Assuming that the data for the pivot table is laid out like this, then all you need to create a pivot using vba are there four lines of code: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= ActiveSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:= "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1) ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Name" ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").Orientation = xlDataField The pivot table so created using the VBA code above can be ...
Continue Reading

Comments (11)

Funny Side Up – I

Posted on 28 June 2009

Funny Side Up - I
What was meant to be a leisurely stroll with my more-expensive half along the beachfront yesterday, turned into half an hour of soaking in the rain followed by about 2 hours of journey back home. While on the way back, I saw a man helping a blind old woman onto the train carriage. A few minutes into the journey, as the station approached, she began walking towards the exit door. Fearing that she could miss her step and hurt herself, I ...
Continue Reading

Comments     

Hide Error Value and Error Indicators in Excel

Posted on 26 June 2009

Hide Error Value and Error Indicators in Excel
To hide error value and error indicators in Excel you can use any of the following means: Hide error values using conditional formatting Hide errors indicators by turning off background error Hide error values by using IF(ISERROR()) function Hide error using a custom VBA function Let's take a closer look at each type of error handling method. Hide error values using conditional formatting Click on the cell that contains an error value. From the menu, select 'Format' -> 'Conditional Formatting'. ...
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