Archive for admin

How to Convert CSV to Excel 2013 Format (XLSX)

There are plenty of apps, and quite a few online services, available online for the simple task of converting the standard CSV (comma separated value) format into the regular Excel formatting than you know and love. Using one of these tools of paying for converters is not necessary for the vast majority of users. Sure, a converter script might be useful when dealing with lots of huge documents, but for day-to-day conversion of CSV files to Excel you can use the following instructions. Read more

Top 10 Excel Tips and Tricks

Microsoft Excel can be one of the most valuable tools available to use on a variety of projects. Once you grasp the basics of Excel, there are several key tips that can make using Excel much easier. The following list is ten great tips you can use to make the most out of this program.

1.) Custom Lists

Creating custom lists is great for repeated information. If you have data that you need to continuously repeat, Excel offers an “auto fill” option that helps minimize the amount of times that you have to re-type the information. For example, if you were creating a student roster and needed to list student names repeatedly, you would go to the Microsoft Office button and click on Excel options, where you would choose “Custom List”. You can choose this on a new or existing spreadsheet. After this, click on the “Popular Tab” (it will say “Change the most popular options”), followed by clicking “Edit Custom List”. Proceed to type the list of names, placing a comma between each. Once the list is complete, click Add and then OK. On the worksheet, type a name from the list, choose AutoFill, and the entire list will fill in.

2.) Linking Data

Excel offers the convenient option to link related data from one worksheet to a separate summary page if you choose “Paste Special”. First, copy the formula you wish to use and then on another worksheet choose which cell you want the link placed. Next, right click in the same cell, choose Paste Special and in that box click on Paste Link.

3.) Sorting Data

Don’t forget about the sort & filter option. This serves as a great shortcut and can make filtering your data very simple.


4.) Shortcuts

There are several shortcuts you can use to quickly navigate and make changes on a spreadsheet. Here are just a few of the convenient shortcuts:
*To apply a time format with hours, minutes, or AM and PM, press Control, Shift, and @
*To enter the current time, click Control, Shift, and :
*To enter today’s date, click Control and ;
*To switch to the next workbook’s window, click Control and F6
*To put a outline border to selected cells, click Control, Shift and &
*To go back and forth between cell values and formulas, click Control and ‘

5.)Filtering Data

This is useful for quickly finding a subset of data in a spreadsheet’s column. You can put a filter on columns that have text or numbers and if you decide to remove the filter simply click on the filter symbol on the column header and select Clear Filter under “Column Name”. It is helpful to remember that a table can go back to cells and the formatting will remain. To do this choose Table, click on Table Tools Design Tab, and choose Convert to Range.

6.) Putting data into a Table format

In order to quickly analyze a group of data, you can turn a group of cells into a table. Several table styles are available, or create a custom style. To make a data table, choose Range of Cells, Home, Styles Group, and then choose to Format as a Table. You can then add elements to it and adjust it under the Table Tools Design Tab.

7.) Creating a Drop Down List

This function lets you make a “quiz” by placing a drop down list in a particular cell. To do this, go to “Data Ribbon” and choose “Data Validation”. When you create the cell’s list, there is an arrow next to the cell. You can then click the arrow to choose the correct response or entry.

8.) Looking Up Specific Data In A Spreadsheet

Using a Look Up function will allow you to search the document for specific information. Once you choose this function, type in VLookUp followed by what you’re searching in parenthesis, and what range of cells to look in. For example, if you were searching for the name John it would look like this: Function=VLookUp(John,A1:D13,FALSE). Typing FALSE tells Excel that an exact match is required.

9.) Hiding Information So That It Doesn’t Print

If there is info on your spreadsheet that you don’t want printed, simply select the row or column that you don’t want printed, then right click on the highlighted information (or the highlighted selection of columns or rows you chose) and click Hide.

10.) Highlight Alternating Rows For Easier Viewing

To do this, highlight the range of information you want to apply formatting to. Then choose Format, then Conditional Formatting and a box will appear. Choose Formula Is in the drop down list, and in the second box type what info you want highlighted. It should look something like this: =MOD(Row(),2)=0. Then click the Format Button to bring up the Format Cells box. Choose the Patterns tab and select a color for the range you want colored.

Using these tips can help you use Excel more efficiently and soon you’ll be breezing through Excel projects like a pro!

“Excel Hacks” – Book Review

Excel Hacks is a largely excellent book written by David & Raina Hawley, founders of Australian Ozgridan 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.

Performance Improvements in Excel 2010

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).

Statistical Analysis – Anova

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.

Displaying Graphs in a Cell

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.

Working With Data In Excel Lists

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.

Preprinted Formats Using Excel Templates

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)