## Excel Ebook

## Hidden Secrets In Microsoft Excel

This ebook from Francis Hayes gives you professional tools to help you get the most out of your Excel program. Any one of these secrets could be the only one that you ever need to know for Excel, but this ebook includes bunches of those tips and tricks! Just think of all of the useful information you can get from it! If you have ever been frustrated at your lack of progress in Microsoft Excel, this guide will teach you everything that you need to know to harness the powerful functions or time-saving elements of Microsoft Excel. Excel is used by offices all over the world, but so few people take the time to teach you anything important about it. Too much time is usually wasted searching the internet for tips on how to use it more efficiently Learn the best way to master Excel in this ebook!

#### 101 Secrets of a Microsoft Excel Addict Summary

Contents: Ebook

Author: Francis Hayes

Price: $9.97

#### My 101 Secrets of a Microsoft Excel Addict Review

The author presents a well detailed summery of the major headings. As a professional in this field, I must say that the points shared in this manual are precise.

Purchasing this e-book was one of the best decisions I have made, since it is worth every penny I invested on it. I highly recommend this to everyone out there.

## An introduction to Microsoft Excel

Excel is a software program that uses spreadsheets organized into workbooks. A spreadsheet is an electronic worksheet composed of individual cells arranged as a grid of rows and columns. Each cell can contain data or a formula used for calculations from information in specified cells. Excel is used for a variety of purposes ranging from simple calculations to statistical analyses and producing charts and graphs, and even as a database. In this and following sections we shall be exploring the use of Excel for these functions, but we will make a start by finding out how a spreadsheet is organized and used.

## Editing plots in Excel

Creating a good chart usually means ensuring it is as simple and clear as possible, so that its message is immediately apparent. Excel allows you to readily access a range of different chart styles the problem is deciding which one to choose for presenting your information. Bar charts are the simplest form of chart. They can be used to show numbers, proportions or ratios. In Excel, the bar charts are available as bars or columns. For the Column chart all of the bars are positioned vertically and for the Bar chart, bars are positioned horizontally across the plot. In Exercise 3.3 we will explore how to use bar charts effectively to present data where we are comparing one or more variables in an experiment.

## Descriptive statistics in Excel

Input the data in Table 4.2 into an Excel spreadsheet. From the Tools menu select Data Analysis. One of the descriptive statistics produced by Excel is the standard error, sometimes abbreviated as SEM (standard error of the mean). There is no function in the Paste Function to calculate this value by itself, so it has to be calculated by using a formula. The standard error is by definition an estimate of the standard deviation of the distribution of the mean, describing how spread out the distribution of the population from which the sample was taken actually is. The mean that is calculated from a sample is never the same as the value for the mean if the data for the entire population were to be included. The standard error provides an estimate of how closely the sample mean represents the true mean for the population. So when the standard error is low, it is more likely that the sample mean is a good reflection of the value for the Figure 4.1 Descriptive Statistics functions in Excel...

## File Conversion

Formats, together with what DAMBE can read in and convert to, are listed below. It is good practice to associate each file format with one particular file type. If you have used Microsoft Office, you will notice that WORD files are associated with the .DOC file type, EXCEL files with the .XLS file type, and PowerPoint files with the .PPT file type. READSEQ is an excellent program written by Don Gilbert, and can automatically recognize and convert many file formats into each other. I personally have benefited greatly from using the excellent yet free program. However, it has five major limitations

## Presenting graphs and charts

Having worked through the previous section you should now realize how simple it is to produce graphs in Excel. What is more skilful, however, is to decide the best plot for the type of data being presented. In whatever branch of science we are involved, observations are made during which we gather data.

## Analyzing the Amino Acid Composition

The amino acid composition (unit 3.2) of the protein will also allow calculation of some basic physicochemical parameters. Using average pKa values for ionizable side chains in proteins (Matthew et al., 1978), the isoelectric point (pI) can be estimated by applying the well-known Henderson-Hasselbach relationship. The calculations can be performed using an electronic spreadsheet such as Excel or via the internet using one of the many molecular biology servers, e.g., ExPASy The values obtained, although only approximate, are useful for guiding the initial selection of

## Framing and gridlines

Most charts benefit from having a frame, especially if they contain gridlines. Excel has the facility to change the background of a chart to different colours. A light shading is preferred so that it does not interfere with the emphasis of the chart. Some caution needs to be used when printing in black and white as even what appears as a pale grey background can spoil the appearance of the chart, particularly where a contrast is needed between the columns on the chart and the background colour.

## Exploring different types of bar charts

In Excel, re-plot the weight loss information this time select the 3-D Column option, placing the data back into columns instead of rows. On the three-dimensional plot it would be more appropriate to have the label 'weight loss'at the top of the axis with the text written horizontally (remember we read from left to right) rather than written vertically so that the reader needs to turn through 90 to be able to read it. To adjust the position of the label, select the Use dots and lines as filler patterns as these give good results. Lines are better if they are slanted rather than horizontal or vertical. Avoid some of the graduated shading that is available in Excel as this may cause problems in contrasting with the background shades.

## Coefficient of variation

This function also does not appear in Excel but is a very useful parameter to calculate. The coefficient of variation represents the standard deviation as a percentage of the mean value it is particularly useful when comparing the reproducibility of results. In quantitative analytical methods, the coefficient of variation is used as a measure of precision in quality control determinations. The coefficient of variation is calculated as shown in Equation 4.4 Enter the data on a spreadsheet in Excel and perform the descriptive statistics on the data. Using the data for the mean and standard deviation for each sample, enter the following equation into one of cells on the worksheet, inserting the appropriate value for the mean and standard deviation in each case

## Database Management Systems for Chemistry and Biology

ChemFinder, from CambridgeSoft Corporation,45 is a small-enterprise DBMS that can be used standalone or connected to Oracle and MS Access. Extension modules for MS Word and MS Excel are available. Accessible through a web browser, the ChemOffice WebServer is a solution platform for chemical and biological data storage and sharing. Its Software Developer's Kit allows customization for user-defined functionality. ChemOffice WebServer Software Developer's Kit extends the Microsoft and Oracle platforms, allowing information scientists to use the most powerful development tools. ChemDraw is the equivalent MDLs ISIS Draw tool for drawing chemical structures.

## The Gene Expression Database GXD

GXD includes images from publications that are curated and annotated. However, in many experiments much of the primary data obtained are never published (in some fields this may be as high as 80 per cent of all data generated), and to obtain these data GXD has developed an annotation and submission tool, the Gene Expression Notebook (GEN). This tool is for use by bench biologists, is Excel based, and allows annotation of experiments such as in situ hybridizations when they are performed. These can then be later submitted to GXD. Development of simple tools such as GEN for biologists are critical if all the 'missing' data are to be accessed and made

## Analyze telomere length mean TRF

To export the measurement of the intensity under the line, click the right mouse button and select Copy. Open an Excel spreadsheet, and paste the data containing the intensity output of every corresponding position from the loading well to the 3 kb. Convert the distance (mm) to the DNA size based on the DNA marker migration.

## Interpretation of the statistical analysis

If we had performed the statistical analysis manually, we would have followed a set formula that would give us a calculated t-statistic (labelled as t-Stat in Excel). As we can see from the table, this value is 0.569 743 7. We then need to refer to a set of tables for the Student t-distribution to find what is known as the critical value that determines whether or not our data are statistically significant at the 5 per cent level. In order to look up the appropriate value, we need to know the degrees of freedom (df) for the data. The degrees of freedom for the Student t-test for independent samples is n-2, so, as there are 23 observations in this example, df 23-2 21. In the Appendix you will find the table for the Student t-distribution. Find the two-tailed critical value for 21 degrees of freedom. The value should be 2.0796. As you will see by comparing the results table in Excel, this value is already provided, as is the critical value for the one-tailed test (1.7207). look at the...

## Oneway analysis of variance

This analysis is applied for one-factor comparisons so for the comparison of the growth of the plant sections, the only factor investigated was hormone concentration. In this situation a one-way analysis would be suitable. However, we will take as our example to work through in Excel an experiment in which the effect of pH on drug dissolution was investigated. A preparation of aspirin containing 100 mg of drug was placed in solutions of different pH for a period of 12 hours in a rotating basket. Samples from each solution were taken at periodic intervals and at the end of the experiment the amount of drug that had dissolved was calculated. The experiment was repeated five times at each pH. The purpose of the experiment was to examine whether the pH of the dissolution medium had any effect on drug dissolution and if so, to indicate at which pH optimum dissolution occurred.

## Least significant difference LSD analysis

So this would be calculated in Excel from the formula In order to find out where significant differences are we must take each set of means for each pH and subtract differences. Using the facilities of the Excel spreadsheet it is easier to rank mean values and then make pairwise contrasts as shown in Figure 5.13. Using the LSD data we can determine where significant differences exist between each pair of means. (In order to report this fully, you may want to calculate the least significant difference at a range of probability levels, 5, 1, 0.5, 0.1 per cent, as appropriate.)

## Freely Available Expert Systems and other Formalized Models for the Prediction of Skin Permeability

Ten Berge32 has also prepared some online software for use. A Windows version of the SKINPERM program is available from his website and formalizes eqns 22-25 for ease of use. Also available from this website is a Microsoft Excel file containing the database taken from Wilschut et al.33 with slight modifications.

## Assay Based on Gas Chromatography

As delineated in Chapter 26, the optimized GC unit is comprised of two instruments, a single sampler, and a common data processing system. The samples are automatically injected (see Note 5). The data are then visualized in microtiter plate format using Microsoft Excel, showing for each mutant -con-version, -ee, and the selectivity factor E for the lipase-catalyzed reaction of substrate 7 (5) (see Fig. 3).

## Sensitivity To Contaminants And Protein Quality

In general, the acquisition of accurate quantitative data on protein interactions will require highly purified, fully active protein samples, but a few of these techniques can be quite insensitive to the presence of contaminants or other proteins present as carriers. This is an area where the SPR sensors particularly excel, and titration calorimetry is also fairly insensitive to contaminants or carrier proteins.

## Inserting graphs and charts

When the new slide is displayed you can double click on the Insert chart button to produce a datasheet and graph. You may enter the data directly onto the datasheet and the graph will be automatically plotted, or import data from a text file in Word, or from an Excel worksheet or insert a chart directly from Excel. It is usually more convenient to create a chart in Excel and then paste it into PowerPoint. Open Excel and insert the information given on the datasheet in Figure 6.3 and create the chart required for the slide. When you have completed the graph in Excel, copy and paste it into the space for the chart in PowerPoint. Re-size as appropriate and add the title.

## Statistics Detecting Differences Among Groups

In this appendix, we will consider some of the statistical tests most commonly used (and misused) in biological research. The tests discussed here are those used for comparisons among groups (e.g., t test and ANOVA). A number of other important areas (e.g., linear regression, correlation, and goodness-of-fit testing) are not covered. The purpose of the Appendix is to enable you to determine rapidly the most appropriate way to analyze your data, and to point out some of the most common errors to avoid. Toward this end, we have included a flow chart to provide a quick guide to choosing the right statistical test (Fig. A.3G.1). Instead of including the voluminous statistical tables necessary to perform these tests, we assume you will have access to a spreadsheet software program such as Microsoft Excel or to a statistical software package to do the actual calculations involved and to supply critical values. We include calculations for some simpler tests to aid in the interpretation of...

## Accessing Computer Tools

If only one experiment is being performed (i.e., one condition, several genes), then analysis is fairly simple and Microsoft Excel is a fine environment for looking at the results (see Internet Resources If the behavior of genes in more than one condition, (e.g., tissue type, patient, time, or drug concentration) are being studied, then more advanced tools such as those described in this chapter are very helpful.

## Imaging and Analysis of Induced Replica Plates

Export the data to a spreadsheet program such as Excel to facilitate analysis. Desirable clones will have two essential characteristics 1) they should be brighter than the wild-type or optimized control clones from previous round(s), and 2) each should be as bright or brighter at 27 C relative to 37 C (see Note 17). Typically, the top 1 3 of the set of optima is used for the subsequent round of shuffling and screening.

## Analysis Of Proteins Using Nearuv Spectrophotometry

Determine the exact peak positions by calculating the first-derivative spectrum in the 280- to 290-nm range. If the spectrophotometer has a built-in derivative calculation ability, choose derivative order 1, polynomial degree 2, and a window of five data points. If not, transfer the spectra in ASCII (text) format (or type it) into a spreadsheet (Excel, LOTUS-123, or equivalent) and perform the calculation using Equation 7.2.1 (Savitzky and Golay, 1964 Steiner et al., 1972), where FD(X) is the first-derivative value at the integral wavelength X, A(X n) is the absorbance values at wavelength X + n (where n -2, -1, 1, and 2), and k is the data point interval (in nanometers). The derivative calculation using an Excel spreadsheet is performed as follows (1) copy the absorbance values of the spectrum into the first column (typically A1 A100 range) (2) fill second column (B1 B100) with the corresponding wavelength values, entering the first wavelength and using Edit Fill Series commands (3)...

## Electronic Registration Systems for the Medicinal Chemist

CambridgeSoft's BioAssay module has been designed to provide an easy-to-use method to upload assay data from multiple sources to a central, secure location. Once the data have been captured, users can perform various calculations, using the program's built-in calculation and curve-fitting abilities. The validated data can then be published to the larger research group with BioSAR Enterprise, which provides storage, retrieval, and analysis of the biological data. In BioSAR Enterprise, users define form and report layouts to combine biological and chemical data. It links the registration system to the BioAssay module to create customized structure-activity relationship reports. The results can be exported to a MS Excel spreadsheet. The fields exported are defined by the form definition, which allows the medicinal chemist to view both traditional numeric and textual data alongside structure data in the spreadsheet.

## Working in Windows

All of the work you do is contained within a rectangular area of the screen known as the window.The background on which the windows are placed is the desktop. Each application that you work with through Windows (such as the word processing package Word and the spreadsheet application Excel) are represented by small graphical symbols known as icons. Your actions in Windows are carried out by using either the mouse or the keyboard, depending on the task in hand.

## Graphs and charts

Drawing a graph in Excel is easy, but does the finished item look right Is it presented as it should be Unless you choose the correct type of plot, producing a graph can go very badly wrong and data can be misrepresented under these circumstances. Let us begin by looking at a simple absorption spectrum. Enter the data onto a worksheet in Excel and then using Chart Wizard select the option for a Line plot (see Figure 3.15). Several types of line plots are shown for you to select the most appropriate. Choose the one described as Line with Markers at each Data Value (i.e. the one showing points and lines). Once At this point look carefully at the graph - can you see anything wrong with the plot (See Figure 3.18.) If you can't, try looking at the x-axis and then think carefully about how you would plot these data if you were doing the graph by hand. You should then notice that the scale is not linear as it should be. Excel has plotted the data as though each wavelength reading is equally...

## Support Protocol

If the instrument's software cannot make such a correction, use a spreadsheet (Microsoft Excel, LOTUS-123, or equivalent see Basic Protocol 1, step 6 annotation, for an example) to generate the light-scattering curve using optical density values at 320 nm and 350 nm. Calculate the light-scattering correction from Equation 7.2.4, where m 64.32 - 25.67 log X.

## Pie charts

Values, particularly where there are several categories, so the pie chart tends to be used for the purpose of providing an overview. By using the feature in Excel to remove a 'slice' of the pie, a particular aspect of the data can be emphasized. Taking the data from Exercise 3.3 (Table 3.3) we will see how to construct a pie chart to represent the decrease in body weight for the male subjects. Using the data on your worksheet, select the data for the male subjects and click on the Chart Wizard button. From the list of available options select Pie with a 3D visual effect. Continue through the chart options to complete the plot which should be similar to that in Figure 3.32. Although the three-dimensional pie is effective it would be easier to judge the different proportions if the position of the pie was adjusted. This is accomplished in Excel by selecting the pie to

## Line graphs

Enter the data from Table 3.4 on your worksheet. Using the option for XY (Scatter) and Data points connected by smooth lines, plot a multi-line graph for both drugs on the same plot. In producing the labels for this plot you will need to insert the units for concentration. These are mg-ml-1. To insert symbols into Excel that will appear on worksheets and in graphs and charts you can use the symbol codes (listed in the Appendix). To insert a symbol press the Alt key on the computer, then enter the numerical code using the Number pad on the right-hand side of the keyboard. On releasing the Alt key, the symbol will appear on your worksheet. Complete the plot by adding titles and labels. You should now be familiar with inserting error bars, so include the standard deviation on your plot, placing + error bars on the upper line and - error bars on the lower line. Your graph should appear as in Figure 3.35.

## Equation 875

Where k0 is the capacity factor of the solute in the absence of the organic solvent modifier, and S is the slope of the plot of lnk versus 9. The values of lnk0 and S can be calculated by linear regression analysis. The underlying principles of an intuitively performed optimization and manually achieved optimization (using Excel spreadsheets, for example, to calculate the ln k0 and S values), or, alternatively, optimization via computer simulation software (e.g., Simplex methods, multivariant factor analysis programs, DryLab G plus, etc.) are essentially the same. However, the outcomes result in different levels of precision. Two representative approaches are collectively outlined below.

## Array Express

Supported queries in the current query interface include query by accession number (experiments or arrays designs), author, laboratory, experimental design, experimental factor and protocol type. Once an experiment, array or protocol is returned the user sees a short text description with clickable links to specific parts of the experiment. These can be explored, and data or array details downloaded as tab-delimited or Excel files. Array information is presented in a format called the Array Definition Format (ADF), which is also used to submit array information to the database. Data are available for both raw and processed data (where these have been provided by the submitter) and can be either exported as a tab-delimited file for use in commercial analysis tools, or exported directly to Expression Profiler, the

## Exercise

Open a new workbook in Excel and enter the data, as in the last exercise, in two columns. The assumptions about the test, Comparing the calculated value of the t-statistic with the critical two-tailed value at the 5 per cent level of significance, we can see that the calculated value is higher than the tabulated value (3.832 2.364). We can conclude that there is a significant difference in the hours of pain relief produced by the new formulation Z compared with the standard aspirin preparation Y and therefore reject the null hypothesis and accept the alternative. As before, Excel shows the actual significance level which is 0.0064 (0.64 per cent). We may make a full statement about the conclusions of the analysis by comparing the means and variance of the data as in the first exercise.

## Data Analysis

Data are either evaluated using the software of the photometer or after transfer to MS Excel. The rate of formazan formation and final OD510 nm values depend on R-PAC concentration. R-PAC concentrations up to 0.5 mM correlate well with absorption at 510 nm measured 2 min after the addition of tetrazolium solution and NaOH (see Fig. 1A). Larger amounts of formazan increase OD510 nm beyond the linear range of the spectrophotometer. In these cases, the initial rate of formazan formation is useful to estimate the content of R-PAC in the sample (see Fig. 1B).

## Data Processing

The individual data concerning the NMR peaks integrated can then be transferred onto Excel spreadsheets. With the help of a macro the ee- or E-values are readily tabulated (4). Fig. 3. Excel spreadsheet of GC data in microtiter-format showing values for conversion (c), ee and selectivity factor (E) for mutant lipases catalyzing the hydrolytic kinetic resolution of alcohol 7 (5). Fig. 3. Excel spreadsheet of GC data in microtiter-format showing values for conversion (c), ee and selectivity factor (E) for mutant lipases catalyzing the hydrolytic kinetic resolution of alcohol 7 (5).

## Equation 712

The simplest method for finding similar genes is to compare the expression pattern for a single gene against all the other genes in the experiments. In Excel, this can be done with complex macros using the above equations (see Equation 7.1.1 and Equation 7.1.2). This finds genes that have an expression profile similar to the gene of interest. Hopefully, the similar genes will somehow be related. Often the goal is to find genes that have a certain similar pattern, rather than a specific gene.

## Scoring the Results

The PIADS Scale can be scored manually or with the aid of a computer, using an EXCEL spreadsheet. Scoring sheets are available to aid in the manual scoring process. To see an example of a completed scoring sheet, refer to Table 2. A blank scoring sheet is also included (Table 1) and distributed with the PIADS questionnaire. An electronic version of the scoring sheet can be obtained by contacting the authors.