Excel Solutions A-C


About statistical analysis tools
Academic Affairs Sharedforms Server (Forms)
Access Data Into and Out Of Excel
Accounts Payable General Ledger Spreadsheet
This demo file will be downloaded
Accounts production software
Acid-Base titration
Add a Drop Down list to a spreadsheet   (no code)
Add a Horizontal Line to a Column Chart
How do you add a nice horizontal line on a column chart, to show a target value, or the series average? The method involves adding a new series, applying it to the secondary axes, and making the secondary axes disappear.
Add New Sheet:
This sub adds a new worksheet, names it based on a string in cell A1 of Sheet 1, checks to see if sheet name already exists (if so it quits) and places it as the last worksheet in the workbook.   A couple of variations of this follow.  The first one creates a new sheet and then copies "some"  information from Sheet1 to the new sheet.  The next one creates a new sheet which is a clone of Sheet1 with a new name.
Adding Range Names:
Various ways of adding a range name.
Adding time values
Add-Ins
Address List, Phone Dialer For Use In
Align (Overlay) Charts
This zipped workbook contains the code that aligns Chart 2 on top of Chart 1, then erases everything out of Chart 2, except for the plotted series.
Align currency symbols to the left
An easy way to round numbers to the nearest 1000
Andrew's Excel Tips
Andy Popes Excel Stuff
Solutions, workarounds and general fun stuff to do with Excel
Another Dynamic Chart
How do you create a chart from a data range that may change in size? There are many cases in which you would like the chart to automatically expand as new data is entered. Here is yet another example showing how to construct dynamic charts.
Arbitrary Axis Scale
Don't you wish you had more control over Excel's chart axis ticks and labels
Arbitrary Gridlines
Don't you wish you had more control over Excel's chart gridlines? Wouldn't you like Excel to draw gridlines where you want them?
Array Formulas
Array formulas
Arrays:
An example of building an array. You will need to substitute meaningful information for the elements.
Astronomical calculations
You can use VBA to provide 'user defined functions' which can be used like ordinary spreadsheet functions. This workbook has a number of astronomical functions defined in a VBA module. Users do not have to know how each function is calculated, they can just use the functions. Functions include trig functions working in degrees, Julian day, sidereal time, obliquity of the ecliptic, coordinate conversions, positions of the Sun and planets and geocentric and topocentric positions of the Moon, rise transit and set time function, precession function
Auction Application -- Custom UserForm, Cut and Paste, Fill editable list box
Audit Satisfaction Survey Spreadsheet
This demo file will be downloaded
AutoChart Manager
Autofilter, Find Excel 2000 Data Quickly With Autofilter
Automating Outlook with Excel

BackSolving for Daily % Gain or Loss
Basic Statistics
Bloomberg Currency Monitor
Boxplots in Excel
Brickyard Chart
I recently was asked about a "Brickyard Chart," a column chart that displayed variable width and height columns, and the columns build on each other. That is, each column's lower edge lines up with the previous column's upper edge.
Broken y axis
Business Solutions

Calculating end of month & adding whole months
Calculation
How Excel calculates
Camcorder, Excel; Screenshots, Creating A Tutorial
Carriage Return - Inserting line breaks in a cell
Cashflow projector
Census 2000
Census data can be used to create sample Databases.
Change A Link In A Formula Without Changing The Formula
Change case from UPPER to Mixed
Change instruments and fields on the fly
Change stored value to displayed value
Change the default folder that Excel/PowerPoint/Word looks for pictures in
Changes the font color of cells that are unlocked and restores locked cells to default color.
Works on all sheets in the workbook.
Changing Fonts and Colors
In a bar graph
Changing Fonts and Colors
In a pie chart
Changing Graphics
Changing Icons to Picts
Changing the case of text in a range
Chart Size on your Menu
This zipped add-in is a simple way to find the dimensions (in inches) of your chart. Select a chart, and the width and height appear right in the floating command bar.
Chart gap for N/A
Charts
Excel Charts -- Links to Samples and Articles
Charts
Chart examples, dynamic charts, statistical chart.
Check box example spreadsheet  (Download)
Check if a directory exists
Check if a file exists
Check Values:
Various approaches that reset values. All of the sheet names, range names and cell addresses are for illustration purposes. You will have to substitute your own.
Class Modules
Classroom Spreadsheet material
Clean up file after Text import
Close multiple files
Collect tick data on more than one field
Colo's Excel Junk Room
Color zone in X-Y chart
Column charts with a difference
Double column charts
CommandBars
CommandBar examples, custom popup menu, CommandBar tools.
Comparative Ratio Analysis
Compare two worksheet ranges
With this macro it is possible to compare the content of two worksheet ranges. The result is displayed in a new workbook listing all cell differences.
Comparing lists
Conditional AVERAGE
Conditional SUM
Confidence Interval for Measured X (Interpolation)
Confidence Interval of the Line of Best Fit
Confidence Interval of the Slope
Contextures
Excel Tips and Techniques
Continually Collect Bloomberg Tick Data
Controlling Futures contract rollover dates with Reuters data
Controls Test Spreadsheet
This demo file will be downloaded
Convert a number by deleting minus sign to ASCII character
Convert a path with a locally mapped drive letter to a UNC path
Convert Numbers into Words; display 32.50 as Thirty-two dollars and fifty cents
Converting Numbers to Text, Add Tick Marks
Converting text numbers to real numbers
Sometimes Excel sees numbers as text and this causes problems. Here are two simple ways that should help both constants and formulas.
Converting Text to Numbers
Count occurrences of a particular formatting
Count Unique Names
Counting
Demonstrates different ways of counting cells in a worksheet.
Counting:
Several subs that count various things and show the results in a Message Box.
Create a log file
Create a scrolling news screen in Excel
Create a Word Document:
To create, open and put some text on a MS Word document from Excel.
Custom Formats in Excel
Excel has custom number formats that can be used on dates, times, text and currencies. There are some practical examples and a link to Microsoft's information on custom number formats that I highly recommend!
Custom function to determine years, months, and days between 2 dates
Custom Radar Chart
 
Valid CSS! Valid XHTML 1.0!
Last edited 07 March, 2010
Links verified 20 July, 2004