Author: Admin

Common Excel Errors

Whether you’re a seasoned Excel professional, a casual spreadsheet user or anything in between, the chances are that you’ve experienced one or two problems when using Excel formulas. Errors in your formulas can get in the way of what you’re trying to do, make your spreadsheet look messy or just be plain annoying! That’s why we decided that it would be a good idea to demystify some of them for you and perhaps give you an idea on how to correct them. Read on to make sense of those formulas that just don’t look quite right. Welcome to The ExcelFrome guide to Excel Errors…

1 – #NAME?

What’s in a #NAME? This error most frequently occurs when there is a syntax error in the formula. It would be advisable to check the syntax of your formula if you’re receiving one of these. One way of doing this is to use the function icon in the toolbar. Other common causes of the #NAME? error are entering text in a formula without using double quotation marks and missing a colon from a range of cells in a formula. For example:

The formula in cell A4 is supposed to read ‘=SUM(A1:A3)’, but there is a misspelling. Correct the formula and the #NAME? error disappears.

2 – #VALUE!

The value in knowing what #VALUE! means is that Excel forces you to check the data type of the cells your formula is referencing. So for example, if you’re asking Excel to add together the values in a series of cells that contains one or more text values, it’ll return the #NAME! error. Ensure your formula only deals with numeric data and the error will disappear. For example:

Attempting to add cells A1, A2 and A3 above returns the #VALUE! error in cell A4 because A3 contains a non-numeric value, ‘Hi’. Removing A3 from the formula, or changing the value in A3 to a numeric one will ensure the error disappears.

3 – #DIV/0!

If you remember your Maths class at primary school, you’ll probably recall being told that you cannot divide anything by zero – or at least if you do, you’ll get infinity. Excel agrees with this and since it cannot adequately express infinity it will happily inform you if you try to bend the rules of division! A way to overcome the #DIV/0! error is to use an IF function so that any potential errors that might be caused by attempting to divide by zero could be handled on your terms. For example:

To avoid the #DIV/0! In cell B3, you could type ‘=IF(A4=0,”Infinity!”, A3/A4)’. This would catch the error and place the word ‘Infinity!’ in cell B3. If cell A4 contained a numeric value that is not 0, the original formula of A3/A4 would calculate as expected.

4 – #N/A

The #N/A error is normally due to your formula failing to reference a value in a LOOKUP function. There are a couple of ways to deal with this error. You can take steps to ensure the missing data is referenced, or you can substitute the error for something else. In this latter case, it is commonplace to use the IFERROR() function. For example:

The cell below Price/Lb in column E is showing #N/A because the VLOOKUP formula in that cell cannot find a price for Banana in the Lookup Table. To correct the error, you could populate a line in the Lookup Table with the word ‘Banana’ and a Price/Lb to its right, ensuring the VLOOKUP range is extended to include the new entry. To remove the error, you could enclose the VLOOKUP formula in an IFERROR function. This could be formed using the following syntax:

=IFERROR(VLOOKUP(D2,$D$6:$E$8,2,FALSE),””)

The inclusion of the double quote marks (“”) at the end of the statement means the #N/A error will be replaced with a blank cell entry.

We hope this has helped you with some of the more Excel common errors. To find out more about what ExcelFrome can do for you and your business, see the foot of this page for details on how to contact us. cartier wb7072k2 replica replica cartier maillon panthere necklace breitling professional 2 bracelet replica bulgari anish kapoor replica iwc 5002 replica Imitation rolex daytona white gold watches for sale

5 Reasons to chose Microsoft Excel over Open Office Calculator

excel training

If you are an IT specialist or an accountant, you may need the services of a suite that has a spreadsheet format. The most commonly used service provider for this is Microsoft with their renowned Microsoft Excel. However, other players have entered the market and provided similar products such as Apache Open Office with their OpenOffice Calculator and Document Foundation’s LibreOffice. There are five main reasons why you should stick with Microsoft Excel over Open Office Calculator.

Variety of viewing Options

Microsoft Excel has a wider variety of document viewing options in comparison to Open Office Calculator. One of the most commonly used options is the diagram creation tool. This ensures that you have a better experience and can play around with the document colours and features to provide a more personalized feel in your end product.

In-built grammar checker

One of its biggest advantages that Microsoft Excel has over Open Office Calculator is that it has a built-in grammar-checking tool that ensures that your document is error-free and flows. Open Office Calculator on the other hand requires you to activate an add-on feature in order to check grammar as you work on your document. This may cost you more money and is likely to fail in some instances.

User-friendly interface

Open Office Calculator uses a single document interface thus each document will have its own window where you can only view one document at a time. However, Microsoft Excel has a multiple document interface with one parent window but numerous documents can be viewed at the same time.

Lower Risk of Security Breaches

Microsoft Excel has a strong built-in firewall that comes along with the package upon purchase that reduces the chances of security breaches. On the other hand, Open Office calculator is open and has a higher chance of security risks such as bugs added to the code by malicious users during development.

Interest of the End User

Unlike Microsoft Office which is proprietary, people download OpenOffice for free use and unrestricted modification without any payment. This translates into the higher concentration of the interest of the end user by the Microsoft Excel as opposed to the developer’s interest as done by Open Office Calculator.

Conclusion

If you already use spreadsheets, it is quite obvious that Microsoft Excel would be a better option in comparison to Open Office Calculator for the above reasons. You should look into other options and make a choice depending on personal preference. In spite of the obvious cost of owning the Microsoft Suite, the program has superior features.

Work Order Control spreadsheet

Our client came to us with a brief to produce a Work Order Control spreadsheet. A customer would send electrical engineering work orders to our client on a daily basis. The client would then assign these orders to a company meter engineer. This was achieved by searching the work schedule to place an available engineer against their customer’s work order list. Then a job would be assigned to the engineer. This was a painstaking manual exercise that took a couple of hours each day to complete. The client wanted an automated solution to the process that saved time and allowed further data to be added.

One of Excel’s key attributes is its ability to automate repetitive manual tasks. The coding language that sits behind Excel is called Visual Basic for Applications (VBA). A developer will write small sections of this code, called macros, to undertake specific tasks. One of the most effective uses of VBA is programming Excel to repeat tasks over and over again, extremely quickly.

The Work Order Control spreadsheet first permits the client to browse to where the source files are saved on their computer. Then the client runs a macro that replicates the manual task of matching work orders to company engineers.

Work Order Control spreadsheet – front end

The user is first presented with a front end spreadsheet (see below):

The user clicks each ‘Browse’ button in turn to navigate to where they saved the two Excel files. In the image above, these are ’2017_SCHEDULE.xlsx’ and ‘work_order_list_schedule.xls’. The location of each file is then stored in the boxes to the left of the ‘Browse’ buttons. Then the user clicks the green ‘Start’ button to run the procedure. The program then runs the VBA code in the background. The entire procedure typically takes between 30 and 60 seconds to execute.

Work Order Control spreadsheet – the procedure

Before they came to us, our client would manually search each work order line by line to match each job to an engineer. Our procedure enables Excel to do this automatically, and more besides. The program stores each work order, according to its month and year, in the relevant Excel tab in the work schedule spreadsheet (see below):

The procedure also searches each work order, line by line, and colour-codes the line. This is based upon whether the order has already been assigned (green), whether it is a new order (blue), or if the order is not found in the work schedule (red; see below):

As each work order row is updated or added to the work schedule, additional data for each order is added from the work order list to its row in the schedule. For our client, the procedure added the work order reference number, the MPAN (Meter Point Administration Number) and the postcode of the meter location.

Work Order Control spreadsheet – results summary

Upon completion, the ‘2017_SCHEDULE.xlsx’ spreadsheet appears on screen. A summary of the number of work orders matched, added or not found is displayed in a message box (see below):

The work schedule is now updated. The client is then able to manually add further detail to the work order rows in additional columns (see below):

How can ExcelFrome help you?

This Work Order Control spreadsheet was written for an electrical meter engineer company. However, we at ExcelFrome can tailor a similarly effective job logging solution for your company, whatever the industry sector. Find out how we can help your business save valuable time and resources with the power of Excel. Please leave your details in the boxes at the foot of this page or contact us via email or phone.

World Champion of Excel? It’s a real thing and here’s how it works

A person can become a skilled user of just about anything. With that in mind, it’s not surprising that there is a contest that discovers who is most proficient at using Microsoft Excel — it’s called the Microsoft Office Specialist World Championship.

The Benefits of Becoming Proficient With Excel

If a person is going to become proficient in a certain type of skill, mastering Excel is an excellent endeavour to pursue. With this powerful software, a person can bring various bits and pieces of data together in a single location. This allows them to create and organize spreadsheets that are mixed with images and text. Users take raw data and reorganize it into data that’s easier to analyse . This can help businesses identify trends, organize their financials and report to shareholders. When a person masters the full potential of Excel then they are able to share that information with local businesses through Excel consultancy, as the specialists at ExcelFrome do locally to Bristol, Bath and the surrounding areas.

A person can also build informative charts when they utilize the power of Microsoft Excel. Data can be filtered, sorted and displayed to present a visual interpretation of any data that’s represented by text. This is a robust tool that can be used to share information with an internal marketing team or analyse e-commerce sales.

How Does Someone Become An Microsoft Excel Champion?

Millions of people around the world use Microsoft Excel for various reasons. It’s the highest used spreadsheet software in the world. Some people work in an office setting where they utilize Excel all day long. Other individuals may use it at home to keep track of sales figures relating to a side business that they operate.

With so many people using this sophisticated software, there are bound to be many individuals who are highly proficient at using its large amount of capabilities. John Dumoulin is one of those individuals who is an expert with this software. He was the winner of the $7000 award after becoming the Excel World Championship. This was the first American to win the grand prize.

The competition for winning this award was fierce. 150 individuals from a list of 49 countries competed for the title. It is interesting to note that John is still in high school! He began his journey while still a student in middle school. He used Excel to create spreadsheets so that he could follow the stats of the Los Angeles Dodgers, his favourite baseball team. It just goes to show that a person does not have to spend years learning Microsoft Excel to become a top-notch user.

Excel Development Portfolio #1.

Excel Development portfolio #1.

A short list of some of the Excel solutions we have crafted.  Is your problem here?

 

Property Portfolio Management Spreadsheet

We built the Property Portfolio Workbook for an estate agency that wanted to keep track of their clients portfolios of property investments. more

Stocks and Shares performance monitor

The client wanted a spreadsheet she that would help him track his shares portfolio.

Using vba, excel can be made to download the latest stock indices from any online share dealing website.

Yahoo financials offer a wide range of financial indices, covering the areas of company details, trading data, historical performance, fundamental analysis and technical analysis.  Including Indices such as market capitalisation, last trade date, volume, 52 and week range & P/E ratio amongst others.

 

 

For this project our client requested share price, dividend dates, maximum and minimum daily change, yield and 52 week maximum and minimum.

Totals such as portfolio value are calculated automatically by the spreadsheet

This project saves our client lots of time every day because he does not now need to open a navigate between various websites to collate his daily shares information.

 

Order Tracking and Invoicing Spreadsheet

We built the Order tracking and Invoicing system for a film processing agency that wanted to keep track of their clients orders as they passed through their factory. more

Stock Control Spreadsheet

Daily work orders updated automatically in Excel

Drawing data from one spreadsheet and inserting it into another spreadsheet is a very common and time consuming process in the business environment.  A process that can be speeded up considerably using vba to automate the process.  This is easier the more standard the structure of the data sets, but this does not have to be a limiting factor.  It simply makes coding a little more complicated.

In a recent example, the client had been receiving daily work orders from their client, and would then have to manually match the orders to the individual engineers in the company.  Excel is very good at matching, and had no problem doing this process automatically.  This project probably saves our clients over 2 hours a day.

Questionnaire Form and Decision Trees in Excel

  • Should you use Excel OR a database?
  • Can a spreadsheet also present elegant forms for the user to fill in, and then store the results?
  • Then can Excel then present the next set of questions dependent upon the previous answers?

Yes.

We have been asked not to show any details of this interesting project.

 

 

 

 

3 Industries which can benefit from Excel consultancy

Excel is often seen as a standard for accountancy, doing simple maths and the filing of numeric data. The truth is though that Excel can do a lot more, the power of Excel just simply isn’t appreciated by many industries. Here are 3 different industries which could be made much more efficient by a full consultancy.

E-Commerce Businesses

With E-Commerce business a lot of manual work is often done with stock control. Whenever new stock comes in or when stock becomes depleted this needs to be updated somewhere. If you run an online e-commerce platform, a spreadsheet and a database this can provide repetition. Excel gives you the ability to link to other sources to import and export data automatically, change a record on your spreadsheet and it can change the record on your online store without anymore manual action needed.

Reports can also be created to monitor stock and analyse where sales are being made, these can either be generated at a touch of a button from within your spreadsheet or automated over time.

If you use online selling sites such as eBay or Etsy then you’re also in luck, these come with APIs which can be easily integrated with your spreadsheets for easy data sharing.

Science

Whilst scientific research will always be complicated, Excel can make it a lot simpler by the use of macros to avoid repetition and by pre calculated formulas that are likely to be reused. A new Excel system can be built to deal with specific data sets, no matter how large these might be. Switching between different types of data to analyse and process results can be made much easier with a bespoke Excel dashboard.

Often there are many solutions out there to deal with scientific needs, one of them being NAG – a comprehensive library of routines, designed to provide efficiency when it comes to science.

Manufacturing

When producing goods from machinery it’s useful to know the volume that can be produced, along with the efficiency of the staff employed to run the machinery. Monitoring software can be used along with a spreadsheet which can produce regular automated monitoring reports. An Excel developer can create a spreadsheet can used to calculate stock levels and export the data off to potential buyers so they know exactly what you have in stock as it comes of the machines.

Welcome to the ExcelFrome blog

Thanks for taking the time to browse our website. At ExcelFrome we are pleased to launch our new blog, offering the latest Excel tips and tricks, company news and the latest from the tech industry. We value all feedback and contributions. If you have a query about anything you find here or would like to contribute then feel free to get in touch on 0800 7723782.

Our experienced Excel consultants are on hand to help you achieve all of your excel goals, make your business or organisation run more efficiently. If there is anything you’d like to discuss or would like a quick quote then feel free to get in touch. We are based in Frome (as the name suggests) and whilst we work locally in Bristol, Bath, Trowbridge and the surrounding areas, we cover the whole of the UK, working both remotely and on site across the country.