Author: Admin

The Benefits of Excel Automation

Software Automation refers to setting up a programme or package to automatically complete a task with little human input. By setting up software to transfer and arrange information for you, you can streamline your workflow at the tap of a button.

Excel Automation is a powerful ally when it comes to improving the productivity of any business. A workforce can never truly achieve their full potential as long as they’re spending endless hours on mundane or time-consume tasks. Luckily, many features of Microsoft Excel can be automated, making laborious processes a thing of the past.

With your employees freed up to tackle more constructive activities, you’re likely to save both time and money, as well as noticing an upturn in productivity. Here’s a look at just a few examples provided by our Excel specialists.

Data formatting

With the correct commands and formulas, you can arrange for Excel to automatically work through your data to display the information you need instantly. This can have a range of practical uses, such as the property portfolio workbook we recently built for a well-known estate agent. Using Excel Dashboards, their employees were able to input the necessary data, which the workbook then used to calculate the desired figures, such as POI and net annual returns. This allowed the company to keep track of their clients’ property portfolios and investments with added efficiency.

Data Validation

Data validation restricts the type of information or value that can be inputted into each individual cell on a spreadsheet. This can refer to restricting individual data entry, or calculating an allowed value from elsewhere in a workbook. This is useful when multiple users have access to a shared system and you want the information entered to be consistent. With automated validation you can set templated dropdown menus for the information you wish to receive, as well as arranging automatic calculations that populate your spreadsheet in the exact format you need. You can configure Excel so this is done automatically before you save and exit.

Importing Information

You company may have its own systems or databases, from which you’d like to export information into Excel. There’s no need to waste time and resources on manual data entry when it’s possible to export this information autom

atically. An example of this is the recent work we undertook for a film processing agency. By exporting and storing data from scanned packages which passed through their factory, they were able to easily keep track of clients’ orders, to provide fast and efficient service. With numerous other practical applications for this system, the possibilities really are endless, and can be tailored to fit the specific needs of your own business.

Merging Spreadsheets and Workbooks

Most companies have numerous databases and spreadsheets. It can be useful to merge spreadsheets in instances where data overlaps, or you require a top-line overview drawn from numerous sources. Whether you want import information from a sales tracker, a revenue breakdown, a budget forecast, or any other type of workbook, Excel can select individual tabs or whole areas to be exported into a different database. Further arrangements and calculations can also be automated for a truly time-saving set-up.

Other Applications

Excel Automation has many other applications, such as saving expense reports for summary and analysis, or exporting reports into other Microsoft Office programmes such as Word and Power Point. With numerous ways to streamline your data, you can discover functions and features that you may not have known Excel was capable of.

If you’d like help, advice, and an expert hand in setting up your Excel Automation, contact us at The Excel Experts, to discover how we can help to improve your business’ data efficiency today.

Why I want a spectroscopic analyzer on my smartphone

I read with great interest about the Hertzstuck sensor, a spectroscopic analyser on a chip. Small enough to attach or even embed in a smartphone, the prospects are truly exciting. Its clear, this technology will put some real power in the hands of you and I.

Essentially, most objects reacts to light in some way or another. An objects colour, is a direct consequence of this reaction.  Taking the idea a step further, shining a light onto an object and analysing the light that bounces off it can tell us huge amounts about the objects deep nature, much much more than just its colour.

Spectroscopy is used intensely In astronomy. The spectra emitted by a stellar object tells us so much about it.  This is largely how we can verify our theories on black-holes, quasars, potentially habitable planets and so on. Telescopes such as the Hubble and numerous land based telescopes use spectroscopic analysers to interpret what they see.

On a more daily basis, such a chip will enable you and I to scan foodstuffs in supermarkets and restaurants and will in many cases tell us exactly what’s in the foodstuff. Is it truly vegan? Does it contain peanuts? All sugars, fats, minerals and contaminants have their own unique spectroscopic finger print. Food that is not fresh has a different signature. Now THAT is power.

Think of how it will stop so much fraud and malpractice that goes undetected normally in foods, fabrics, pharmaceuticals and ‘cheap imitations’  …. Now I’m waiting to load that data into Excel and do some interesting science on it. tag heuer formula 1 diamonds replica fake rolex watch test replica tag heuer watches breitling transocean replica uk iwc portofino review replica

My big Spreadsheet is driving me crazy!

Do you dread opening a spreadsheet? Worried that its going to crash, act sporadically or just not even load?

If it does load, does it take a very long time to open? and when it does, is it sluggish and tiresome to use? Do you find yourself swearing at your spreadsheet?

These are complaints with Excel that I hear from potential clients every day, in my role as ‘Customer First contact’ at The Excel Experts Ltd.  The reason I love my job, is because I can assure the caller, with absolute certainty, that these problems can be resolved.  I can feel the relief on the caller’s mind which is so rewarding as I know how they feel only too well.

Is this a Big Data Problem?

When a caller starts to share these types of problem with me, the first question I ask myself is “Is this a big data problem?” The first sign of a big data problem is usually the size of the workbook. The newest versions of Excel can handle huge amounts of data, but if you want Excel to handle it efficiently, you may need some help from us.

In these cases, using a more powerful PC will not solve the problem. If you have too much data in Excel, Excel will start behaving oddly and even crash.  Clients are often at the end of the tether and about to throw their computer and spreadsheet out of a window! They sometimes think the only solution is to move all of their data over to a custom-built programme but that is not the case. Thankfully, there are ways to solve this problem and keep using Excel.

Or inefficient coding?

Often, the time you spend waiting for Excel to perform a function is being increased because the code that it is running is not very efficient. In these cases, we can often speed up a macro considerably by rewriting parts, or sometimes the whole of the code.

Another option when dealing with big data is running the process overnight with an automated batch file, this means you can sleep soundly while your computer does all the hard work.  A further solution that has helped many of our clients is to use a much faster machine that runs in the cloud, such as an Azure Virtual Machine. By allocating huge resources to a virtual machine to process, this speeds up the macro considerably.

Often a business will regularly add data to a spreadsheet, knowing very well that they are gradually compounding to a problem. In these cases, a different approach to loading the data can help matters considerably. Excel has some power tools such as PowerQuery and PowerPivot that enable it to deal with huge amounts of data, but in a very efficient way. Not for the faint hearted but that’s where we come in.

Another solution is to utilize a database of some kind. One of Excel’s biggest strengths is in its ability to connect to almost any data source. We have numerous ways that data can be leveraged so that the spreadsheet itself stays small but can still access huge amounts of data.

We know the power of efficiently and effectively utilising business intelligence and data and we also know how frustrating it is when too much of your time is taken up with slow computing processes. Big data brings huge benefits to your business but can also drive you crazy as described above. Please don’t despair and don’t give up on Excel yet! There is a silver lining, Excel does have the tools and abilities to handle this data quickly and efficiently, if you know how.

Types of Excel Specialists that can help your business

When introducing new Excel solutions to your business, you may feel you’d benefit from the help of a specialist, to take your Excel systems to the next level. It’s worth noting that due to the huge potential of Excel, it’s used in a wide cross-section of organisations, with numerous uses and many different practical applications.

For this reason, our team at The Excel Experts offers a range of specialists, each suited to different aspects of Excel, and different industries. Take a look at some of our main areas of expertise, to discover how our team can best help you and your business.

Excel consultancy

For Excel assistance, an Excel consultant can be invaluable to your business. Equally well suited to large corporations or small self-employed businesspeople, your consultant will be available to help streamline your workflow using Excel. Available for either remote or face-to-face assistance, they can show you in easy-to-understand steps how to employ Excel to automate time-consuming tasks, cut down manual data entry, and ultimately save you both time and money.

Ecommerce consultancy

One of the most common areas of Excel is the ecommerce sector, and as such we’re able to offer Excel experts who specialise in the online retail environment. From creating shared reports that can benefit an entire team, to analysing data at management level to assist in business strategy, a sales-focused Excel advisor can help you achieve your long-term goals. Excel in the ecommerce world can also be invaluable for freeing up valuable employee time, by automating laborious manual processes, allowing your employees to focus on more practical activities.

Accounts consultancy

To help ensure your accounting is maintained effectively, our team include specialists with both backgrounds and qualifications in accounting. An efficient accounting system can help businesses to effectively grow, not just by assisting with budget templates and asset reporting, but by also helping to reduce tax liabilities. Our financial specialists can also assist with staying on top of your compliance obligations and responsibilities – something which may prove difficult for busy workplaces or small businesses.

Scientific consultancy

Microsoft Excel is not just used for business, it’s also a powerful tool in the science and research sector. Whether planning work, recording and analysing results, or managing finances, Excel is just as invaluable in the research world as in the commercial one. We pride ourselves on recognising the different approach required in these fields, and as such are able to offer Excel specialists with scientific background who understand the needs and requirements of your organisation. Whether you’re looking to develop a new Excel system or optimise an existing one, our scientific Excel consultants can help you achieve the level of accuracy and analysis you require.

Excel Experts

Whatever your Excel requirements, we have a specialist to suit your individual needs. What’s more, we’re able to train and advise your employees so they’ll be competent in maintaining Excels systems once they’re set up. By equipping your colleagues with a full working knowledge of Macros, Data Manipulation, Pivot Tables, VLOOKUPs and more, you’ll find your organisation becomes more efficient and resourceful. Additionally, we also offer installation, training and support across the entire Microsoft Office suite, providing you with the full benefit of Word and PowerPoint too.

How to list Excel and Microsoft Office skills on your CV

Almost all office jobs now require basic levels of computer literacy, so displaying that you have knowledge and experience of Microsoft Excel and the wider Office suite can be invaluable. Where many candidates trip up however, is when effectively displaying this on their CV.

With so much information to include, such as qualifications, employment history, practical skills, and a personal statement, it’s important to know which aspects to keep to a minimum, and which parts to build out.

When covering computer skills, many candidates will simply list ‘Microsoft Excel’ with no further details. As Microsoft Office programmes have a huge range of functions and features, this is both vague and unhelpful for an employer. Additionally, it’s essential to display your own skill level, to distinguish yourself from other applicants.

Provide Details

If you’re competent in Microsoft Excel, don’t just write the programme name on your CV. Prove your skill level by mentioning the features you’re familiar with. A knowledge of Macros, Pivot Tables, Data Manipulation and VLOOKUPs is highly desirable by many employers, and displaying your awareness and familiarity with these functions is invaluable.

This can be applied to other Microsoft Office programmes too. If you have experience in PowerPoint, you can mention your knowledge of Slide Masters, SmartArt, Animations, and importing data into a presentation. By including these details, you’re indicating from the outset that your knowledge is genuine, and letting an employer know exactly what you can do.

Provide Examples

When applying for a job, listing your skills is a great start. To really impress an employer however, it’s best to provide examples of how you’ve used these skills in the past. This will not only back up your claims, it will also give an indication that you know how to apply these skills effectively.

If you’re just starting out on the career ladder, don’t be afraid to list university experience. For example, if you created financial models as part of a research project, then be sure to mention it. If you have experience in the workplace, such as creating a sales report or analytics database, don’t leave this out. You can state this alongside your skills, as part of your employment history, or within your personal statement – just ensure that your valuable experiences aren’t hidden from view.

Be Truthful

Whatever you write on your CV, the most important rule of all is not to lie. Regularly, candidates list a desired skill on their CV under the assumption that if it’s required, they can learn it on-the-job, or at the last minute. However, if a job is advertised as requiring a certain skill – for example an understanding of pivot tables – it’s not uncommon for a short test to be built into the interview process. If you list skills you don’t have, you’ll be wasting both the interviewer’s time, and your own.

When job hunting, it can be daunting to constantly see lists of desired skills that you may not yet possess. There’s no need to worry, however, as help is at hand. Here at The Excel Experts, we don’t just provide business excel consultancy, we also offer training across the entire Microsoft Office suite. We can deliver introductory, basic or advanced levels of help, via online courses, remote assistance, one-to-one training, or group sessions. We can help you work towards an Office qualification, alongside giving valuable experience, which will be invaluable to your CV. For more information, don’t hesitate to contact our friendly team, today.

How to use Microsoft Excel on your smartphone

In today’s busy society, it can’t always be possible to complete 100% of your workload from the comfort of your workplace. Meetings, visits and commutes are all commonplace, making it essential to be able to access and edit your files on-the-go.

The ever-increasing power of smartphones means this is easily achievable on a hand-held device, which is lighter and more convenient to travel with than a bulky laptop. Microsoft Excel, along with the rest of the Microsoft Office suite, are all available in full as mobile apps, so there’s no longer the need to carry around that heavy laptop at all times.

The Microsoft Excel app is available on devices with a screen size of 10.1 inches or smaller, and is free to download to your handset. You can perform basic functions such as view, create and edit spreadsheets for free, however to use more advanced features, you’ll need an Office 365 subscription.

Microsoft Excel for Android

To use Microsoft Excel on your Android handset, the first thing you’ll need to do is download the Excel app onto your phone. You can do this by opening your Play Store app and searching for Microsoft Excel. To use the app, you’ll need to be running Android KitKat 4.4 or higher, and be using a handset with at least 1GB RAM. If you don’t already have a Microsoft Office subscription to use the software, this can conveniently be purchased through the app, and billed to your Play Store account.

Microsoft Excel for iPhone

Apple users can also enjoy the power of Excel in the palm of their hand as the app is similarly available through the App Store. To download Excel for your iPhone, you’ll just need to make sure that you’re running iOS 10 or higher. As with the Android version, you can make purchases through the app, making it easy to set up an Office subscription if you don’t already have one.

Microsoft Excel on the go

Designed specifically for use on smartphones and tablets, the Excel app includes the important features you’re used to on the desktop version, such a formulas, charts, tables, and conditional formatting. You’ll be surprised how easy it is to use Excel on a touchscreen, which lets you tap to add data, as well as simply dragging rows or columns to format quickly.

Perhaps the most convenient thing about having Microsoft Excel on your smartphone is being able to update and share files wherever you are. By accessing Excel through Microsoft OneDrive, you can synch your files with your desktop so your important documents are never out of reach. If you have a whole team working on one file, you can track all amendments made, or for more advanced collaborations, utilise SharePoint to manage files and folders with teams right across your business.

If you need help making Excel work for your business, then don’t hesitate to contact us here at The Excel Experts. With years of experience behind us, we’re ready to help you and your organisation get the most out of your spreadsheet systems. Our dedicated UK Excel specialists can help you unleash the power of Excel with either remote assistance or face-to-face visits, wherever you are in the UK.

Supercharge Excel with VBA

VBA, visual basic for applications, is a common coding language that can be used by Excel developers to automate the program to perform a wide variety of tasks. The use of VBA allows for a much more customizable problem solving approach to Excel problems rather than a one size fits all approach. Because the language is relatively old and very well known, a plethora of resources exist to help novices learn the basics of the language.

Types of Tasks VBA Can Help With

The automation of a mundane and repetitive task would be the most common use of VBA. For example, say you had two lists of numbers in the first two columns of a large spreadsheet. For each set of adjacent numbers, you wanted to add them together and display the sum only if the sum was greater than 10. In cases where the sum exceeded 15, you would want the cell to be bolded and red.

Rather than going through the trouble of writing an Excel formula, populating cells, and then using conditional formatting, you could write a VBA code that could accomplish all of those tasks at once.

In addition, the code would be transferable to other files that you had that needed the same formatting done. VBA also offers the flexibility to create tables, insert new sheets based on conditions, and anything else the coder can think of. VBA code is written in a separate window from the actual Excel file and is run when the coder presses a run button. The code can then be saved and used with other files.

Learning the Language of VBA

VBA problem solving is not possible if the user is not able to code it themselves or find someone who can. Fortunately, there are many free VBA courses online. A good starting point would be a simple Google search on a beginners’ guide to VBA. Several YouTube series exist on the topic as well for those that prefer to watch and follow along as they go.

The key though to learning any coding language is practice. In order to be a successful VBA coder, practice is needed to develop the skills that the platform requires. Many practice problems can be found with these courses or by hiring an Excel specialist but users may be able to think of problems on their own that they can try to solve themselves.

Improvements with Excel in 2017

Excel has gained many new features and improvements this year, with even better features announced for release next year. This year’s updates include better background removal of images, support for collaborative editing, and automatic file saving. The features added this year have helped to make Excel better for both collaboration and note-taking.

Better Background Removal

Excel 2016’s February update added better background removal. While the ability to remove the background of an image has existed since 2010, but this year’s updates have improved its capabilities drastically. Rather than cropping out the background using straight lines alone, users can now use a free-form select tool to better fit the borders of the desired object. Additionally, Excel automatically detects the general background to eliminate the need to draw a rectangle around the foreground.

The New “Dubai” Font

Microsoft has worked with the Executive Council of Dubai to create a new font with support for Arabic and Western European languages. In March, Excel and other Office products began to include Dubai alongside their other fonts. Designed with legibility in mind, the Dubai font is very easy to read.

Better Access for Subscripts and Superscripts

This addition is a minor one, but some users will find it quite useful. In June, the Quick Access bar received an option to include the superscript and subscript buttons. Users writing mathematical or chemical formulas will now be able to write their equations without constantly switching tabs.

Collaborative Cloud Editing

Teams can now work on the same Excel workbook at the same time. When “co-authoring”, users will see changes from other users within seconds, similar to how users can work on the same sheet in Google Sheets. After uploading your workbook to OneDrive, it becomes shareable with any number of users for easy collaboration.

Co-authoring also automatically enables AutoSave. As its name suggests, this long-awaited feature will periodically save your files to OneDrive. With this, the old advice of “save early, save often” is made redundant by automatic saves every few seconds. Co-authoring requires AutoSave, but Autosave can optionally be enabled for any file uploaded to OneDrive.

Better Note-Taking

Excel 2013 was the first version to add support for drawing tools. These tools, supporting both mouse and touchscreen input, helped users take notes better by allowing for both visual and textual notes. Beginning with the June 2017 update, users are able to create a custom “pen set”, which synchronizes across all of your Windows devices. Additionally, the October update added a pencil texture for users that desire a more realistic note-taking experience.

For more information on how you can get the most out of Excel and the recent updates, see the Excel services on offer from The Excel Experts.

Productivity Is Key

At The Excel Experts we like to try out new writing talent. This short story was written by a young lady in America.  I like it, what do you think?

Productivity Is Key

Business owners can find keeping up with speed in changes in technology difficult.  Proactive business owners understand that investments in technology can benefit a company financially and give a company a tactical advantage over the competition. To Investigate advances in technology, to determine if they offer a positive return on investment for your company. Just as Josh does….

Josh is the kind of business guy that doesn’t know how to cope with the speed of technology advancement, although he is super handsome. On this crazy day, Josh sees Mary, a colleague that’s doing great with technology, and using it to develop her business and increase productivity. He couldn’t face her, he wants to learn but doesn’t know how to get her attention. But he found a way around it.

Josh dresses up like a piece of art work, hangs on the wall like a piece of modern art.  People walk through the art gallery, looking at the rather odd pieces of  art until a couple of women, KATE and MARY stop in front of him.

KATE: What’s this?

MARY: It’s called “Pity the Fool.”

KATE: It’s amazing.  The man looks so … lifelike.

MARY: It’s like he could walk right out of the painting and talk to us. (Suddenly JOSH’s eyes pop open and the women are startled).

JOSH: Do you have any water? (His eyes close again.  Kate and Mary look at each other and then at the painting again).

KATE: You heard that right?

MARY: Yeah.

JOSH: Water.

KATE: I’ll get some water. (She leaves.  Mary wants to follow and doesn’t want to be left alone).

JOSH: Wait.  Please. (Mary reluctantly stops.  She looks around but appears tob alone.  She cautiously returns).

MARY: What?  Uh… who?  I mean…

JOSH: Why am I here?

MARY: Sure… let’s ask that question.

JOSH: This is my self portrait.

MARY: Pity the fool?

JOSH: Yes.

MARY: Well… it’s working.  I feel sorry for you.

JOSH: Everyone does.

MARY: Oh… (Mary looks around nervously hoping Kate will return.  Josh climbs down off the background and sits at the edge of the art looking very hurt and sad. Kate returns with some bottled water).

KATE: Found some water. (Josh takes it from her and gives a weak smile).

JOSH: Thank you. (Josh drinks and lets the water refresh him.  Mary and Kate look at each other and then at Josh nervously). I have being trying to get your attention Mary.

MARY: really?

JOSH: Your business is growing everyday while mine is suffering, could you please tell me how you’ve being able to cope with speed of technology advancement?

MARY: Have you heard of this website

JOSH: No, what’s it about?

MARY: That is the website of ‘The Excel Experts’. They are all about increasing peoples productivity with spreadsheets and office productivity software. This will bring your business a lot of benefits.

JOSH: Spreadsheet?

MARY:  Yes, a spreadsheet is a piece of software that can analyze and store data in tabular form. It looks a bit like a grid.

JOSH: Oh I know! … most of my staff use them. They spend AGES every week doing Payroll and matching stuff up and making reports and things…. Yes very useful.

MARY: Well spreadsheets can also be made to do complicated and repetitive jobs with incredible speed and accuracy. It may be that you could save your staff hours and hours a week, by getting Excel to do lots of the work for them.

JOSH: Excel?

KATE: Excel is the most widely used spreadsheet – honestly Josh don’t you know anything?

JOSH: You mean it’s really that simple … like breast feeding?

MARY: Yeah, huh… how do you know breast feeding is simple?

JOSH: Well.. software that can do everything you just mentioned really…is simply breast feeding you isn’t it.

KATE: I think he’s right

MARY: Really, what if I had told you earlier that spreadsheets are useful for home and business users of all skill levels. An underlying benefit, no matter how you use them, is the relative ease with which you can turn raw data into useful information. The built-in calculators, formulas and functions in spreadsheets save time and improve mathematical accuracy.

You can also use formulas and functions to manipulate text strings. For example, you can separate first and last names from a full name or find and replace words or characters in a string with another word or set of characters. Formulas can link and instantly update cells in the spreadsheet should the value of a cell change. If you do make a mistake, built-in error-handling capabilities flag and provide information about the incorrect cell value.

JOSH: Oh my God, this is no longer breast feeding.

MARY: Then what is it?

JOSH: It’s heaven on earth…

KATE: I really need to start taken advantage of this new software so that I won’t hang on the gallery wall like Josh.

JOSH: thanks for that heartfelt compliment… Mission accomplish, I’m getting my spreadsheets software today…

How to Integrate Excel into Your Web Applications

Learning how to Integrate Excel into your web applications will definitely improve your presence on the Internet. Savvy online consumers are looking for quick, easy to understand information on products and services, our Excel specialists have a lot of experience in this field and are please to offer the following advice on integration.

Transferring Data Via Excel

Although transferring data via Excel sounds complicated, it only takes a few minutes to send data directly to a web page of your choice. In fact, Excel programmers have already done the hard work. Simply follow the directions.

Once you have all the required information on the spreadsheet, do not forget to save your data. Then, click on “File” in the upper left corner. Among the options, scroll down to “Publish”. The program will ask for a selection of the material to be published. Then, type in the URL where the data will be uploaded. In seconds, your site will display professionally crafted information.

Working with Web APIs

Working with web APIs does not necessarily require a professional programmer. Everyone familiar with the Internet has probably used an Application Programming Interface. An interface is simply the connection between a web page and an interactive application.

For example, Websites sell products online. In order for consumers to pay for purchases, an interactive application is necessary. Rather than programming their own interactive software, a business may ask PayPal, or a credit card company, for the code needed to place their interactive application on a web page.

The customer is directed to the payment site. Once the transaction is complete, the buyer is automatically redirected back to the seller’s web page. In many cases, API code is free to use, based on the mutual benefit to all parties.

Converting Excel Workbooks to Web Applications

For consumers to interact with the data provided, and Excel workbook must become a web application. Once again, Excel developers have anticipated the need to manipulate information on the Web. Simply select the document to be published as a web page. Then, the program provides a box to be checked, if the page is to be interactive.

Once the document is titled, and uploaded, a visitor can use the Excel menu tools to rearrange the data to his/her specifications. The program asks if the data should revert back to the original setting, once an individual is done using the application.

Integrating Excel into your web applications is easy and a definite boost for your web page.