How to Convert XML to Excel?

When sharing or importing and exporting data between services, XML format, along with CSV are some of the most commonly used formats. Both of these formats are readable by many software and services, and allow for easy further manipulation. However, if we want to perform some kind of statistical analysis on either format we need to import them into software for data analysis.

As you might have guessed, one of our favorite software for data cleaning, manipulation and analysis is Microsoft Excel. Unlike PDF, which has to be converted into Excel to be able to use the data (you can convert it with Cometdocs), XML and CSV can be imported into Excel fairly easy.

We have already shown you how to import CSV into Excel, and vice versa. Now, we are going to show you how to import XML format into the amazing Microsoft Excel.

All you need to do is go to the Data section. Choose From Other Sources – From XML Data Import.

 import xml data into microsoft excel How to Convert XML to Excel?

After that what Microsoft Excel will do, is to use the internal structure of the XML file to accurately import it into rows and columns in the spreadsheet.

import xml into excel How to Convert XML to Excel?

You will actually be surprised to see how accurate it is.

the newly converted table will be waiting for you How to Convert XML to Excel?

Another useful tool that we discovered is a tool which uses Excel to convert XML to  CSV format. We found it to be great in working with large XML files, you should check it out as well. The tool is: http://sourceforge.net/projects/convertxmltocsv/

Convert images (jpg, png, gif) to Text, Word or Excel format with Cometdocs

With the rise of mobile scanning devices, everyone can now scan a document into an image, and save it for further use. However, the text that is trapped inside scanned images in formats such as jpg, png, tiff, etc. can not easily be recovered. A special optical character recognition technology is needed to be able to make that text reusable and editable.

Having that in mind, Cometdocs has implemented new conversion types, which enable users to convert images into text, Word or Excel format.

convert image formats to word excel Convert images (jpg, png, gif) to Text, Word or Excel format with Cometdocs

The conversion quality is high, and the recognition technology is one of the best available on the market.

Keep in mind that the quality of conversion will depend on the clearness of the text in the image. So if the image is blurry and the symbols are barely visible, no recognition engine will be able to accurately convert them. Also if you used the mobile scanned for scanning, make sure that the document was in a straight position, without being sidelined.

The supported image formats are jpg, jpeg, png, tiff and gif. The conversion process is the same, simply  upload the image into Clipboard.

Drag it into the Convert tab, and after that choose one of the offered formats. The options for formats are txt, docx and xlsx.

And that is it! Feel free to let us know how  you like this new conversion format and stay tuned for more  changes.

 

Working with SmartArt Graphics in Microsoft Office

Microsoft Office’s SmartArt Graphics is a powerful feature that is often underused.When writing a document or creating a presentation, it is always recommended to present information and concepts in a visual way. This allows the reader to better comprehend everything you are trying to say.  Your readers or audience will be more easily engaged if you use graphics and visualization.   The point of the report or presentation that you are sending to your boss or teacher will be clearer and more concise. On the other hand, if you want to present your information better to your students to ensure that they understand concepts correctly, using visualizations is a must.

Microsoft Office has all you need for creating visualizations, diagrams and plans. There is no need to use external software when everything you need is in the software itself. All you need to do is choose an appropriate SmartArt graphic.

There is a wide variety of choices. There are choices for graphics that can be used to present information in the form of a list, a process, hierarchy, a circle, pyramid, and relationships in an efficient and appealing way.

All you need to do is to go to Insert – SmartArt  and choose the appropriate graphic. You will probably need to experiment with a couple of different ones, but without a doubt, you will find one that fits your needs exactly.

insert smartart graphic Working with SmartArt Graphics in Microsoft Office

1) Inserting Text

Inserting text is easy. All you need to do is click on the Shape and enter the text.

text pange in smartart graphics Working with SmartArt Graphics in Microsoft Office

You can also enter bullet points by clicking on SmartArt Tools – Design –  Insert bullets.

add bullet to text Working with SmartArt Graphics in Microsoft Office

To change the text color, font size, or other details, simply right click on the Shape.

change text color and size Working with SmartArt Graphics in Microsoft Office

2) Implementing 3-D graphs

You can turn every graph that you insert into a 3D graph by going to SmartArt Tools – Design – Styles, and choosing one of the 3D options.

3d graphics Working with SmartArt Graphics in Microsoft Office

3) Change SmartArt Graphics’ color

change color in smart art tool Working with SmartArt Graphics in Microsoft Office

If you don’t like the default blue color of the graph, you can easily change it by simply going to SmartArt Tools, and choosing the option Change Color.

4) Change color and layout of specific shape

change shape look Working with SmartArt Graphics in Microsoft Office

Right click on each shape to see all of the available options for change. You can change the shape look, layout, color, transparency, edges and more. This is especially useful in cases where you want to have shapes of different color in order to differentiate between subjects.

format shape Working with SmartArt Graphics in Microsoft Office

5) Add new shapes

Sometimes you want to add more levels of shapes in SmartArt Graphics. For examples instead of 4 elements in a circle, you would want to have 5 elements.

add shapre to smart art graphics Working with SmartArt Graphics in Microsoft Office

This can easily be done by going to SmartArt Tools – Design – Add Shape, or by right-clicking the Shape – Add Shape – Add Shape After/Before.

Example of Future Value Calculation in Excel

Future value tells you how much money you could have in the future if you invested a certain amount of money today with a certain interest rate. This can be easily calculated in Excel, and we will show you how.

The simple version:

Image you have $100,000 and you want to invest them in a bank for six years with an annual interest rate of 3 percent. How much money will you have in six years from now?

The image shows what your calculation will look like.

simple equation for future value Example of Future Value Calculation in Excel

The equation for that is:

FV = PV * (1+i)n

n is the number of period units, in our case — years.

i is the rate of return (interest rate), it is expressed in numbers like 0.03 or 0.04.

FV is future value

PV is present value

Microsoft Excel calculation:

In Excel there is a function for calculation future value, which is more complex because it describes a more complex situation.

excel future value Example of Future Value Calculation in Excel

It is assumed here that each period you invest a constant sum of payment, and each period you receive an interest based on the all money invested thus far.

Here is the formula Excel uses for calculating the future value.

formula future value excel Example of Future Value Calculation in Excel

  • Rate-  Interest rate per period.
  • Nper- Total number of payment periods.
  • Pmt-  Payment made each period; it cannot change during the life of the annuity.
  • Pv-  Present value of your investment.  If Pv is omitted, it is assumed to be 0 (zero), and you must include the Pmt argument.
  • Type- Optional. It can be number 0 or 1, and indicates when payments are due. 0 means at the end of a period, and 1 means at the beginning of a period.

So, for example, let’s say that your initial investment is 300 dollars, and that each year in the next six years you invest 1,000 dollars. You’ll also receive an interest every year, which is being invested as well. The interest rate is 3%, and payments are due at the beginning of the period.

fv function in excel Example of Future Value Calculation in Excel

How much money will you have earned after six years?

The answer is $7,020. See if you get the same result.

excel funkcija Example of Future Value Calculation in Excel

Linear Regression Analysis in Excel

Regression analysis is a statistical process for establishing connections between certain variables. For example, we have one dependent variable and we want to determine how much other independent variables affect it. That is how we get a model of interdependence, and we can use it to predict the dependent variable’s value in the future. Excel uses the linear regression model. We’ll show you how you can apply it in Excel 2013 and 2010.

After you’ve prepared data for analysis, you need to turn on the statistical analysis add-in.

You can do this by going to OptionsAdd-ins. 

excel add ins1 Linear Regression Analysis in Excel

Click the Go button.

Check the first option which is used for statistical analysis.

excel linear regression Linear Regression Analysis in Excel

Now click on the Data tab and then double-click the newly introduced button Data Analysis. Choose Regression.

regression analysis in microsoft excel Linear Regression Analysis in Excel

Now you need to select data for analysis. In the field where it says Input Y range, you should select a column or row with the history of the dependent variable values, the variable that is affected by other variables and whose future value you want to predict.

input x y range Linear Regression Analysis in Excel

In the field Input X range select fields with independent variables. You can select only one variable, and that is called a simple linear regression. In case you have several independent variables, you have to place them next to each other in the document and select them that way.

selection of variables in excel Linear Regression Analysis in Excel

 

Select Labels if your data have a mark (the first field with the variable name). Also choose where you want the results of the analysis to be shown (in a new sheet or new document). After clicking OK, the analysis results will appear. As you can see, all important parameters for the analysis are there.

regression analysis output Linear Regression Analysis in Excel