Fresh Excel

Spreadsheet Tips and Tricks

January 21st, 2011

“Excel Hacks” – Book Review

2 Comments, Books, by admin.

Excel Hacks is a largely excellent book written by David & Raina Hawley, founders of Australian Ozgrid–an online authority on Excel and VBA. The title is somewhat discouraging; using the word “hacks” doesn’t inspire confidence at first, but the authors do.

What the book covers:

  • Reducing Workbook and Worksheet Frustration
  • Hacking Excel’s built in features
  • Naming hacks
  • Hacking Pivot Tables
  • Charting Hacks
  • Hacking Formulas and Functions
  • Macro Hacks
  • Connecting Excel to the World

Pros and Cons

The first chapter is about reducing the frustration that an Excel user indeed might run into, for example when trying to personalize  the interface, weeding out unnecessary prompts, locking cells or reducing Excel workbook size.

Furthermore, the book deals with a large number of tips to help you better pivot tables and charts, including a dynamic approach to various input data, e.g. making the SUBTOTAL formula dynamic.

If you are an avid user of Excel, this book is truly fun to read and clearly ahead of most traditional Excel textbooks that often do no more than present Excel’s functionality. The tips you get in Excel Hacks are not normally found in other books.

On the downside, the reader should have an Excel skill level above novice user to be able to absorb as much as possible of this book, even though all Excel users can probably find some gems in this book.

Excel Hacks was written in 2004 and is therefore quite old. On the other hand this isn’t necessarily a problem because the issues tackled in the book are mostly timeless.

October 13th, 2010

Performance Improvements in Excel 2010

No Comments, General, by admin.

Various performance improvements in Excel 2010 can help you interact with data more efficiently. Specific improvements include:

* Overall improvements in response to feedback from customers has been the performance of Excel 2010 improved in several areas. Excel 2010 is faster when you move and resize the chart, work in Page Layout and interact with the shapes on the worksheet.
* Support for larger data sets of Excel 2010 can handle the work books with large amounts of data more efficiently. Above all, it takes less time to perform activities normally done on larger data sets, such as filtering and sorting data, copy and paste data from one spreadsheet to another and use the fill feature to copy formulas.
* Multi-core: Improved multithreading in Excel 2010 helps you to speed up the process to retrieve, sort and filter data in pivot tables and Excel tables. Moreover, it is generally faster to open and save large files than before.
* Faster calculation If workbooks necessary for critical business processes within the organization can not estimate the speed be a bottleneck. In order to achieve faster calculation performance has Excel 2010 support for asynchronous, user-defined functions that can run simultaneously without using multiple Excel calculation threads. This is useful when you import data into spreadsheets in custom and in high performance computing (HPC scenarios).

October 13th, 2010

Statistical Analysis – Anova

No Comments, Statistics, by admin.

Microsoft Excel provides a set of analytical tools – Analysis ToolPak – that you can use to easily and rapidly perform complex statistical or engineering analysis. You specify the data and parameters for analysis, appropriate statistical or engineering macro functions used in the tool and display the results in an output table. Some tools generate charts in addition to the output table also.

Related worksheet functions Excel provides many other statistical, financial and technical worksheet functions. Some of the statistical functions are built, others you will have access to when you install the Analysis ToolPak. Develop data analysis tools Analysis ToolPak includes the tools described below. You access them by clicking on the Data Analysis Tools menu. If the Data Analysis command is not available, you must download the Analysis ToolPak.

Using the analytical tools Anova you can make different types of variance analysis. Which tool you use depends on how many factors and samples you have from the populations you want to test.

Anova: Single Factor

With this tool, do a simple analysis of datavariansen between two or more samples. The analysis tested the hypothesis that each sample is taken from the same underlying probability distribution against the alternative hypothesis that the underlying probability distribution is not the same for all samples. If there are only two samples, you can use the worksheet function, TTEST. However, if you have more than two samples, you should use the Single Factor Anova instead because generalizations can be made with the TTEST.

Anova: Two-Factor With Replication

This analysis tool is useful when data can be classified into two different dimensions. In an experiment to measure plant height is given seedlings fertilizers of different types (eg A, B and C). The plants are placed even in places with different temperatures (low and high). For each of these six possible pairs {fertilizer and temperature}, we have an equal number of observations of plant height. Using Anova tool we can test the following:

1st If plant height for the different fertilizers come from the same underlying population. The temperature is ignored in this analysis.
2nd If plant height for the different temperature levels from the same underlying population. Fertilizer ignored in this analysis.
3rd When we took into account the effects of different fertilizers in step 1 and the different temperatures in steps 2 retrieved six samples that meet all value pairs of {fertilizer and temperature} from the same population. The alternative hypothesis is that the effects achieved on the basis of specific pairs {fertilizer and temperature} in addition to the differences that are based on only fertilizer or only temperature.

Setting the input area for the tool Anova

Anova: Two-Factor Without Replication This analysis tool is useful when data are classified into two different dimensions in the case of Two-Factor With Replication. When this tool is used, we assume that there is only one observation for each pair (for example, each {fertilizer, temperature} pairs in the example above). Using this tool, you can apply the tests in steps 1 and 2 of the case Anova: Two-Factor With Replication but have not yet enough data to use the test in step 3.

October 13th, 2010

Displaying Graphs in a Cell

No Comments, Graphs, by admin.

With the arrival of Excel 2010 there’s a new tool that complement the graphics in Excel. These are the sparklines or graphics from a single cell. With them you can now create tiny integrated graphics within each cell. These will be used to easily detect patterns of data tables. This is a very simple and quick to highlight important trends that data (such as seasonal increases and decreases).

In this little mini tutorial for Excel will see how they are generated:

To make use of sparklines we will only select the cells where we place these graphics in miniature (usually a column next to the data table). Then, click on the Insert tab–sparklines, choosing row, column or gain or loss. In particular, the choice seems more visual in the online format. The format with gain or loss is very suitable for interpreting balance sheets.

Mark the range of cells that we use as the basis of the graphs and these will be generated instantly.

It is very fast, simple and intuitive interpretation of the data table, identify trends and monitor developments. What were initially quite difficult to read a set of numbers has now become easily understandable.

In previous versions you would have generated a graph that includes all the series (if you use the same scale) or a chart for each of them.

The value offered by this new feature of Excel 2010 is evident.

October 13th, 2010

Working With Data In Excel Lists

No Comments, Basics, by admin.

It is important to understand what lists of data in Excel are and how to create them. Too often, the structure and field names used in the data lists are not descriptive. This can cause problems when analyzing the data with the tools of Excel (Pivot Tables, Solver …).

When creating a list of data must be taken into account:

• All records in the list of data share the same field names. For example, field names used to store a single datum. For example, using multiple fields (name, first name and last name) instead of a single field (full name).
• All values of each field must be consistent. For example, if data from the Total Sales field with two decimal points, another record should not have four decimal places.
• No value of the fields should be empty, even if the value is unknown. In this case, you would enter 0. By leaving a blank data value, it can sometimes be difficult to make the calculations or the results we get are incorrect.
• It is better to create only one list in each spreadsheet. Tasks such as sorting, grouping and filtering work best when limited to single lists in a single worksheet.
• The field names should be descriptive but not too much. For example, a field called Name is more descriptive than a field called Country A. also a field called Total sales could be as easy to understand as a field called Total Sales shown in the bottom of an invoice.

These tips will help prevent errors in our calculations and we offer the possibility of using other tools of Excel more efficiently.

October 13th, 2010

Preprinted Formats Using Excel Templates

No Comments, General, by admin.

Using some of these methods will help you increase your productivity because you no longer have to resort to filling forms by hand or typewriter. It has many advantages creating a template for this work.

The idea is to create a comprehensive system that serves to fill out any preprinted form is a system devoted exclusively to print pre-printed formats. The aim is to stop filling our forms by hand and use the power of Excel to fill in any format, invoices, checks, referrals, contributions, taxes and any format that is necessary and that it was printed and fill it only remains. We have built a step by step which allows quick and easy way to print any format. At present the forms to fill out is tedious and lends itself to make mistakes so if we were wrong to use or purchase must fill out another form and waste time and money. So these templates to automate all the tasks of filling forms or forms.

* Requires minimal knowledge of Microsoft Excel
* As a template is used only once for life
* If you change the format only needs minor adjustments
* As a template I can fill a variety of formats while
* You can protect the cells, leaving only the filling so that the cursor jump just filling fields
* You can create formulas and perform calculations for the form fields are filled automatically
* You can use bold and underlined in some areas to highlight the time of printing formats
* We use forms for invoices, but you can get with other manufacturers and also serves
* You can take a copy to oriiginal and print 2 copies
* I can send to your stationery and use this template to systematize
* Keep a copy of the format in a database
* You can use an invoice number for the example
* You can leave default information in your template to avoid having to type
* Convert the total points value (function created by us)
* We will provide support for its format is optimal to fill it
* We have a lock application where we will make our product unique to your help
* For an excel template and not a program (because we can create from the simplest to the most complex)
* I can leave the form and print multiple templates
* You can organize your forms and each have a database
* You can only protect the form and make a key change
* You can generate a row for their size and numbers automatically
* Can extract the date automatically days, months, years, weeks using Excel functions
* You can add, subtract, total, and any formula applicable to your form can even create your own formula
* You can access your network from a template and print format on a network printer
* You can for example print the original copy in a printer and the Draft in Another
* You can print format remotely using any program connection (explain how)
* You can print from your template to a remote printer (explain how)