Excel does many useful things and entering data so that you can do calculations or manipulate data. However, sometimes you need to bring that data in from somewhere else or you need to export it to be used in another application.
Data can be imported from either Microsoft Access or from another text file such as a CSV file (comma separated value), TSV file (tab separated file) or other format.
Excel also lets you export to these text based formats too.
Thankfully, Excel does this with ease. Here are two video tutorials to show you how to do either.
If you have watched any presentation over the past couple of decades then the chances are you have watched a PowerPoint presentation. If you need to create your own presentation and have either not done one before or need a refresher because you have not done one for a while then the tutorial below will help you get started.
I must admit I have seen some dire presentations sometimes by good lecturers and all they need to do is follow some of these tips.
Some tips when doing presentations are:
Be consistent with fonts, do not use different fonts on every line. Try and stick to the one font throughout.
Don’t use too many different animations.
Make sure that you use a font size that is not too small, 24 point is a good minimum but this can depend on the size of the screen, the quality of the screen or projector and the room size. Also take into account the age of your audience.
Be careful with colours, make sure they are clear and follow guidelines for colour blindness. Avoid using orange, red and green in your template and text. See more about accessibility on Microsoft’s website, click here.
Don’t cram your slide with information. These should just be prompts or useful images, videos, audios or charts.
Keep the number of slides to a minimum.
Be early for your presentation and test it. Make sure you have more than one copy. Don’t rely on being able to download it.
Press F5 to start on Windows or Shift+Cmd+Enter to start on a Mac. It is frustrating for your audience to watch whilst the presenter is fumbling to get their presentation started.
Practise. Also make sure you are familiar with the system if you are going to be switching from PowerPoint to YouTube, Word, Excel etc.
Don’t stand there reading what is on every slide. The audience can do that.
There is more coming soon on how to use PowerPoints features so keep a look out here and on my website www.jargonfreehelp.com.
Analyzing data in Excel is one of its great features and there are a number of tools for doing this, aside from sometimes just changing a value and seeing what happens. If you have done this then there is an easier way. This works really well if you have one or two values you need to change.
Lets say you have a formula or function for calculating repayments on a loan and you want to see what would happen if the interest rate changed or the term that you were paying it over changed, then a one input data table could be used to display your options. You could use a two input data table if you want to see what would happen changing both.
Here are two tutorials for each of these different types of data tables.
From a technology point of view I didn’t feel that this was a year where there were many “wow” moments, if any at all, and the usual leaders in wow, Apple, seemed a little flat. In fact like many it seems as though iOS 7 didn’t hit the mark particularly with their new design. However, there were some things that caught my eye that I found particularly useful. I’m sure there is your own list but as I said these were some that caught my eye.
1. Tagging Files in OSX Mavericks.
Lets face it so many of us have so many files we often spend too much time looking for them and wondering which folder and perhaps even which device we stored it on. When OSX Mavericks came out Apple did their usual fanfare but honestly the only thing that made me sit up and notice was the ability to tag files with keywords and big have I put this to good use. I generate lots of files from documents to photos to video, I have thousands of photos.
Now if you have a Mac and you have Mavericks you have to try this, if you give them keywords, and each file can have more than one. For example, if I had a bunch of photos taken in London of buildings I tagged them up with the keywords London, building, architecture and perhaps the name of the building if I knew it. You are going to find this one of the most useful things. I did. Here is a little video I made.
2. 4G
I had to get another phone earlier this year, this was in addition to my iPhone as I needed one for work and one for personal. So I opted for a Samsung Galaxy S4. I did this because as a technologist it is always good to know both sides of the story and having an iPhone and a Galaxy gave me that perspective. It was interesting but one of the other reasons was the 4G capability and I was pleasantly surprised by how fast it was. Also using it as a wifi hotspot meant I could work with a good connection when out and about, assuming I had a 4G connection. As luck would have it I did when I needed it. It also came in handy when we had a problem with our landline connection at home which also resulted in losing broadband for about a week just as I was doing some important work.
Here is a little video I made on how to turn the Samsung Galaxy S4 into a wifi hotspot. Oh, and here is a link to one on how to do the same with an iPhone.
3. FaceTime Audio
Not everything was disappointing with iOS 7, although I still don’t like the interface and preferred the calendar in previous versions. Let’s not forget the searching feature, yuck. However, one feature I did welcome was FaceTime Audio. I find FaceTime useful but don’t always need to see the other person, it could be that I haven’t got a great connection and audio only uses less bandwidth or because I just want to use it as a phone as I’m walking along. Whatever the reason I have been using this a lot more. If you have iOS 7 when you go into your contact you have two icons to tap on one indicates a camera which is FaceTime with video and the other is phone handset which indicates FaceTime Audio, just tap on the one you want.
4. Windows Surface 8.1
Now this was a surprise. There I was minding my own business at work when I was asked if I would like to try a Windows Surface 8.1, this is the RT version. I had tried these when they first came out at an expo. I switched it on and I was pleasantly surprised. Now I like my iPad but as many of you know I like using Excel, a bit obvious from some of my previous blog posts. Now the great thing about the Windows Surface and other Windows 8.1 tablets is that you can use Word, Excel and PowerPoint, the RT version doesn’t have Access but I believe the really 8.1 does. Nonetheless I was pleased. Using Excel 2013 really does work well as do the other Office programs.
Another benefit was I could plug in a memory stick, hooray, now the other thing I would say is be careful as you can plug it in but if you put it down in a hurry you could bend it, so I used it with a short USB extension cable.
It is bigger than my iPad mini which is excellent for many other things but ability to use Office is winning me over. I’m now considering if I should have a proper Windows 8.1 tablet that allows me to install Access and other software.
I wonder if Apple will make something similar, aside from the iPad they haven’t made any of their computers touch screen.
5. Learning Online
There is no doubt that watching video online has exploded over the past few years and clearly I’m an advocate for learning online using video and there were two that caught my eye this year. First up was espares.co.uk. The rubber seal on the door of our washing machine needed replacing there was a tear in it but it wasn’t leaking…yet. My wife looked up how to fix it and came across eSpares, they supply parts for all sorts of household machines but it was their videos explaining how to do it that caught my eye, very clear and straightforward. Needless to say the whole lot including getting the tools, which they had came to less than £32. There was also a sense of satisfaction of doing it myself. If you’re like me and don’t mind having a go then check it out.
Here is the video I watched for our washing machine.
Another one I found on YouTube which was a bit more fun was martyzsongs, he has easy to understand videos on how to play songs on the guitar. Now, I am a terrible guitar player but I enjoy it, I never get very far. Marty Schwartz, no relation, has plenty of songs and the sort I like. At the moment I’m trying to learn how to play Johnny B Goode by Chuck Berry, wish me luck. Here is Marty’s video I’m using.
If you have been following my blog since the beginning of this week you will have seen that I have been looking at action queries in Microsoft Access. These are queries that are using for changing the data sets such as adding data to them, deleting data and also archiving data.
Here is the last one of these which is the update query. Update queries in a way are a bit like find and replace but can do so much more. For a start you can get it to make changes to data based on specific criteria and allows you to do more than find and replace. It can also be used to update values, for example, a particular manufacturer may be increasing their prices by 10%, using an update query you could easily do this. Another example I have used before is when area codes changed for phone numbers, I used the update query to make that change to just part of the text.
As many make resolutions at this time of year here are some you could add to make your life less dominated by IT and relax a bit more. Let’s face it technology should be there to make life easier and more enjoyable.
1. Turn off your work email, and personal one for that matter if they too are getting on top of you, on weekends and holidays. I recently went on holiday and decided this time to turn it off completely and you know what, I felt no temptation to check my work emails. Like me I’m sure many of you check to stop a build up of emails for when you return. I let people know I wouldn’t be around and there weren’t many when I got back.
For a change I felt like I had a real holiday.
2. Don’t check your emails at weekends or evenings. We’ve all had that email that we’ve seen on a Saturday morning or before going to bed that has just made us angry. Believe me it is better not to check as it is unlikely you can do anything about it anyway.
3. Are you always running low on battery power or phone going dead towards the end of the day, just when you need it most. This is when you are calling to say you’re on your way home, please pick me, put my dinner on or perhaps you want to use your remote heating app to turn on the heating, got this coming in an attempt to reduce my gas bill.
The good news is modern phones have lithium ion batteries and you don’t need to let them run down before charging up so you can give them a boost any time you get the chance. These batteries do not have the memory problem of other battery types. Another thing you could do is get one of those portable battery packs that can give your phone a boost or if you can change batteries then get a spare battery.
4. Backup. Now that word just seems to invoke the thoughts of a chore but it needn’t be. It might seem like a chore but when something goes wrong you will agree how valuable the time taken to backup has been.
Backing up to the cloud is great assuming you don’t have hundreds of photos and videos to back up regularly. Dropbox and Carbonite are two great options.
If you are backing up to a hard drive then back up to two, yes really, and then keep one at home and one at the office (or somewhere else), swap them over once a week or once a month depending on what’s convenient. If you’re like me where you are constantly taking photos and video you might want to do this more often. These are valuable files and unlike documents are difficult to recreate.
5. Speed of your connection. This might not seem important but are you getting the speed that your broadband provider is suggesting. Use www.speedtest.net to check. If not chase them up they might be able to do something.
By the way, turning off your modem when you are away on holiday can cause your connection to slow down as the providers system may change your profile if it sees no activity.
6. Stop being tied to your phone. Did you really need to look at Facebook or Twitter. I know I’ve done this when I’m with someone and I didn’t need to do it. I see people at restaurants taking ages as they fiddle with typing something in on Facebook telling the world what a marvellous meal they are having. The person with them looks bored!
7. Now my one sister reckons she doesn’t have time to learn how to use her iPhone, Excel, Word etc better yet when I watch her she either muddles her way through it the long way around or she doesn’t do it as she doesn’t know that any of these are capable of doing what she want. Recently I finally got her to watch one of my tutorials on how to remove duplicates from a list, I knew she needed to do this. Eureka! Where she had been working around this now in minutes she had done what she needed to do. You can see if any of these tutorials are useful for you on my website www.jargonfreehelp.com
I look up tutorials for things I need to know in 2013 I learnt an awful lot including how to fix our washing machine.
Hope these help make your 2014 a little more relaxing. These are all things I implemented in 2013 and yes I learnt to relax, I was overdoing it and can now see I was.
So you’ve imported some data into your Microsoft Access database and now need to add that data to an existing table. It would be handy to copy and paste but using an append query does the trick and you can also just append specific data based on criteria.
Appending imported data is straightforward, however, sometimes the field names of the imported data don’t match those of the table you are importing into. Using the append query deals with this.
As always before doing this test out how it works on a copy of your database and remember to always backup your data.
As the names suggests this type of action query is going to delete data so if you are doing this for the first time try this on some data you are not worried about losing or on a copy of your data. You should always have a copy of your data particularly if you are going to use queries like the delete query. So before you do anything please make a backup of your data.
So you may need to delete data for data you no longer need or because you have already archived it and you can now remove it from the table. If you want to know about creating an archive you should take a look at the make table query I have here.
Microsoft Access has different types of queries to do everything from just viewing selected data to archiving it using a make table query, deleting data, appending data and updating data.
For the next few days I will be adding video tutorials on the make table, delete, append and update queries. Today the tutorial is about the make table query. I usually use this to either archive data or to extract some data so that I can work with a smaller dataset.
Using the make table query does not remove any data from the original table it just copies the data over. However, it is always a good idea to have a backup of your data.
Excel is great for dealing with numbers but it also has functions for dealing with text. You can check in a bit of the text by extracting it using one of the functions like LEFT, RIGHT and MID. You can also check on how many characters are in a string of text using the LEN functions (LEN for length), very useful for checking that the correct amount of text has been entered such as a credit card number.
Gary Schwartz's Blog 