Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'



Use MS Excel during Eskom load shedding

With Eskom’s load shedding in full swing, some thoughts on using MS Excel to allow you to continue running your business.

With no power your main systems may not work or you may not be able to print the documents you need to continue working.

Perhaps your POS devices don’t have a backup generator and instead of stopping business you continue and record the transactions in Excel (as long as you have a charged battery in your mobile device or laptop, Excel keeps working- for awhile at least)

Some useful tools below and further down some thoughts on planning with regards to Eskom power failures.

Entering today’s date (quickly without needing to look at your cell phone)

Need to retype today’s date or time. Quick shortcut for getting Excel to do it for you without you needing to look at your watch or cell phone (UPDATE- Thanks to Chris- on other keyboards the : (which is the SHIFT ; on my keyboard) is elsewhere so for time use CTRL + :).

Date Time Stamp

Can’t print but need to compare multiple spreadsheets at the same time

Desperately need to compare 2 spreadsheets or do a recon but you can’t print them out to use a highlighter on. Use Excel’s tools to show multiple workbooks AND MULTIPLE WORKSHEETS at the same time.

Capture data like a typewriter (on enter it goes to the beginning of the next line).

If there is lots to capture, learn how to automatically go down to the starting point of the next row of data (bit like a typewriter if you are old enough to remember).

Create your own drop down list from the data entered above

Tired of retyping the names, client numbers, addresses? Use this quick drop down trick so that it looks at all the information you have previously captured and gives you those options as a dropdown (sorted nicely), like the image below.

Drop Down Generator

Use Excel’s inbuilt quick calculator

With your systems down perhaps you need to do some quick calculations on the captured information. Please don’t pull out a calculator. Rather use Excel’s Status bar.

Status Bar

Change your Autosave settings to avoid losing work

If you work on a desktop and you are scared that power may suddenly switch off, change your AutoSave settings to save more regularly.

Eskom load shedding

Data Validation

Data validation allows you to control what is entered into a spreadsheet. If you plan to upload a spreadsheet to your accounting system for example, you want to make sure that your staff enter account numbers, client names etc correctly. Data Validation allows you to control these aspects. By combining it with a VLOOKUP you can even make it easier for your staff to capture data. So by using Data Validation to choose a client number, VLOOKUP can pull through the name, address, discount terms etc.

The one benefit of the Eskom problem is we have opened up the Data Validation lesson so that you can see how.

Understanding how dates work

Dates are a major issue in Excel. If you don’t understand how they work you will always be surprised when you can’t format a date or dates sort in a strange way. Making sure you understand how Excel dates work and what your accounting software needs could be the difference between a quick upload of a thousand transactions or manually retyping the thousand transactions (and hoping the electricity doesn’t fail while you are doing it).

We have opened up the understanding dates lesson for you to learn more if you prefer to read the text but below a video clip.

Using Excel while the power is down

Below some overall thoughts that will make it easier to capture data while the power is down.

Recording transactions

If you use Excel while your system is down to record transaction, plan a little bit. The key is to build the spreadsheet with the end in mind i.e. if you record sales in a spreadsheet, find out the easiest way to upload it when the power comes back on. Can your system handle an upload and what must it look like (how many columns, what order must the columns be, are their restrictions in what can be input into the columns).

Once you know this make the spreadsheet as similar as possible to what is required. Then when the power comes back you can easily (and quickly) upload and switch back to using your accounting software instead of manually recapturing and wasting time between two systems.

The above rules are similar to what you should be doing if creating a dashboard or management report in Excel.

We have opened the key lessons from our dashboard course that help with this. You can see it on the Getting the Data Right module (free previews) available in the Excel for management reporting course.

Collecting information from multiple people e.g. sales forecasts

Perhaps you need to collect information from staff all over the country and the web based system is not working during load shedding.

Again Excel is useful for collecting information but make sure you plan with the end in mind. Setup a template that everyone must use but make sure the template is setup in such a way that it will be easy for you to combine when you receive the hundreds of spreadsheets.

If it is a significant number of spreadsheets, do you know there is a new free tool in Excel can consolidate hundreds of spreadsheets very easily. Have a look at reasons to learn about Excel’s Get & Transform tool.