I have transmission data of UV spectrophotometer in MS Excel and would like to know how it can be convert into absorption data. Please tell me about this.
It all depends on the nature of the sample and how you did perform your measurements. What kind of sample do you have? The formulas given above are valid only for very diluted samples and only if you have recorded your measurement against the pure solvent. Otherwise using the formulas above can result in errors up to 1000% and you have to use e.g. Abeles matrix formalism which correctly accounts for multiple reflections at the interfaces.
It is really important to know what you are doing, but doing it manually repeated times gives no new insight...
A more comfortable way would be to use the optical spectroscopy software Spekwin32, which allows to copy&paste columns of x-y spectral data to directly create a spectral plot. This can then be easily displayed into all axis types. Start from here: http://spectroscopy.ninja
There is also a guidance document for the Copy&Paste feature: http://www.effemm2.de/become/guidance.html
In general, a spectrophotometer working in the solar radiation wavelength region measures either the transmittance or the reflectance of a sample. That is, the absorbance is not measured directly, but is calculated from the measured transmittance and reflectance (if both can be measured). When a spectrophotometer gives you the absorbance A’ on the logarithmic form, i.e. the optical density OD, which is a common output from many spectrophotometers (OD = A’ = log10(1/T) = alog10(e)x = a’x, "10" supposed to be as a subscript here, i.e. the 10-log, where T is the transmittance, x is the penetration length or depth x, and a and a’ denote the absorption coefficients depending what form is used, which is deduced from the Beer-Lambert law), it is in reality a calculation from the measured transmittance ignoring the reflectance. A spectrophotometer measures the radiation which is collected by the detector from either the transmitted or the reflected beam. Furthermore: T + A + R = 1 (100 %) where T = transmittance, A = absorbance and R = reflectance between 0 to 1 (or between 0 to 100 %). Note that the absorbance A in the above is not the absorbance A’ on the logarithmic form, i.e. not the optical density OD, which is a common output from many spectrophotometers. You may see further details in e.g. chapter 3, 4, 7.6.3 and 7.6.7 in the following article: B. P. Jelle, ”Solar Radiation Glazing Factors for Window Panes, Glass Structures and Electrochromic Windows in Buildings - Measurement and Calculation”, Solar Energy Materials and Solar Cells, 116, 291-323, 2013. In addition, you need to have control over if the transmittance and reflectance are specular (directly transmitted or reflected, i.e. mirror-like) or diffuse (scattered in all directions), where you in the latter case would use integrating sphere accessories to collect the scattered transmitted/reflected radiation.
Why does everyone here think that "absorption data" means absorbance? It could be absorptance (1-reflectance-transmittance-scattering) be as well... ;-)
absorbance has a couple of shortcomings. I think its use needs to be "rethought":
First of all it is not directly accessible by measurement, as -log10T (or -log10(R/R0)) usually does not give absorbance.
Article Employing Theories Far beyond Their Limits-The Case of the (...
In addtion, it is not proportional to the electric field intensity.
Article The Electric Field Standing Wave Effect in Infrared Transmis...
Article The electric field standing wave effect in infrared transfle...
Furthermore, absorbance is not per se additive and proportional to the concentration, although for not too high concentration and oscillator strength this might not be a proplem:
Article Beer's Law – Why Absorbance Depends (Almost) Linearly on Concentration
Overall, it leads to believe that things are very simple in vibrational spectroscopy, while very often they are not. It reminds me very much on the simplified language that was promoted for the people in George Orwell's "1984" to prevent them from thinking about things... ;