A great overall tutorial if you want to get the basics right. Don’t let the version (Excel 2003) put you off as there are few changes between the various versions of Excel – especially as you are starting to learn the language.
On http://www.ozgrid.com/VBA/ you will find great bits of code samples for every day needs, however it is not an end-to-end tutorial. Copy and paste for sure!
Come to think of it, I also like http://www.cpearson.com but you are probably quite busy with the above already!
Created for a client who needed to use these as criteria in Excel queries (in fact a Microsoft Query, more about this later), this Excel worksheet shows Excel functions used to determine date ranges such as Monday of this week, last Monday, last Sunday, next Sunday, next Monday etc.
A quick guide to the functions used: TODAY() – displays the current date. WEEKDAY(serial_number, [return_type]) – returns a number which represents the day of the week e.g. Monday = 0. Change the return_type if you prefer Sunday to be 0. Very useful when you know that today’s weekday is 0 and adding 7 then means Sunday. DATE(year, month, day) - returns a date from three different numbers e.g. DATE(2012,12,31) returns the last day of the year. YEAR(serial_number) – returns the year for the date entered as a serial number.
Using Excel Functions to calculate dates and date ranges
Learn how to use the PowerPoint shapes and drawing tools to create your own shapes, from simple to elaborate. You never need to pay for your cliparts again if you have the patience to draw your own.
Did you have a bit of a shock when you received your payslip at the start of the year? Did you gasp? Did you reach for your calculator to double-check the amount but then realised you were a little confused by the Universal Social Charge (USC) calculation rules given by the Revenue?
This is where my Universal Social Charge (USC) Calculation in Excel workbook comes in handy. Here is a quick tour of the USC Calculator Excel workbook.
Download the Excel Workbook Universal Social Charge (USC)
How to use the Excel Workbook Universal Social Charge (USC)
The workbook opens to display a table of contents. Click USC Calculator.
Type an Annual Gross Pay amount in the red box. The calculations are shown in the grey cells. Click Copy the formula.
You can copy the formula and paste it into your own workbook. Just make sure to update the cell reference as indicated. Click USC Rules and Calculation table.
If the rates change, use this page to update them. You can also customise and print the Ready Reckoner (table) to have a quick reference handy.
I always love it when I train people who have limited computer skills and I get to show them copy and paste or cut and paste. While initially confused by the concept, they soon adopt the clipboard functionality and it becomes their number one instant productivity tool.
Cut, copy and paste can be used to move folders around the computer, get files into folders, insert pictures into documents, duplicate text across files. And because it has become so ubiquitous, we take it for granted.
When the iPhone was launched in 2007, its lack of clipboard functionality caused much criticism. When finally added, cut and paste was celebrated around the blogosphere iPhone finally gets copy and paste.
And when we take things for granted, we sometimes use them without thinking as with this example of typographical instructions left behind, taken from the excellent HeritageWeek.ie. Can we guess? The event organiser (Chester Beatty Library) emailed their listing with special instructions for spelling. The website editor copy-pasted the whole thing and did not even need the instructions at all then.
So be careful that it doesn’t happen to you! Learn how co cut, copy and paste with the clipboard and marvel at the time savings offered by that simple feature. If it didn’t exist we would have to invent it again.
Previously we created a canned answer in Outlook (a message template in Outlook). Now is time to save even more time and discover how to put that canned answer only a click away instead of browsing for it each time.
Video: Outlook canned answers at the touch of a button
Create a new shortcut button to a message template
Right-click any toolbar and select Customize in the contextual, pop up menu.
In the Customize dialog, select the Commands tab. Under Categories, click File. Under Commands (the right part of the dialog box), hold the left click button on your mouse or pointing device and drage Mail Message into a toolbar. In this example I drag the button to the New dropdown.
The new command is now visible in the toolbar menu New. Do not close the Customize dialog box.
Right-click the New Message command. In the contextual, pop up menu, type the Name to display for this command (instead of Mail Message, I called mine Welcome Message)
Right-click the command a second time and this time in the pop up menu, select Assign Hyperlink and Open.
In the Link to File dialog box, browse to the message template you want to associate with this command button. Did you make a note of it as suggested in this tip?
You can close all dialog boxes now, including the Customize dialog box. You now have a command button which starts your message template.
One advantage of using the SUMIF function to get subtotals is that it is not necessary to modify the order of the records in the original database unlike the Subtotals feature as shown in Super easy subtotals in a Microsoft Excel database.
Video: More easy subtotals in a Microsoft Excel database with the SUMIF function
More easy subtotals in a Microsoft Excel database with the SUMIF function
Using the SUMIF function to calculate the total by region
The list of regions is in D4:D28 and is the first argument
The Criteria is either the name of the region (Sligo) or a cell which contains the name (L4)
The Sum_Range is the list of values and it must match the list of regions i.e. same height.
Using SUMIF in a Summary table
You can see in how the values calculated in the table of the left are obtained with the SUMIF function. Note that by using absolute references ($D$4:$D$28) it is easy to copy the formula and extend it to all rows at once with AutoFill.
Why not follow us?
from RSS, e-mail updates, share us on Facebook or just bookmark us in your browser. We have new tips all week long and they never take more than two minutes.
You need two files to follow this tip: an Excel spreadsheet with a chart or some data in it and a PowerPoint presentation.
Video: Get Excel charts and tables always up to date in your Microsoft PowerPoint presentation
Get Excel charts and tables always up to date in your Microsoft PowerPoint presentation
Copy the data or chart from Excel
Switch to your PowerPoint presentation and select Paste Special from the Edit menu
In the Paste Special dialog box, choose the Paste Link option.
The object you are inserting is recognised as Microsoft Office Excel Worksheet Object.
It will show as Microsoft Office Excel Chart Object if you started from an Excel chart.
Update Links
When you open the PowerPoint presentation in the future, a message will warn you that the presentation is linked to other files and it gives you an option to Update Links and see the latest figures – or to cancel and see the slides with the figures as they were last shown.
Not everybody is as confident with Microsoft Access databases as you are. Most users feel intimidated and afraid – rightly so – to make mistakes. So make their use of the database easier with big navigation controls. In a previous tip, you learned how to Find records faster with a dropdown in Microsoft Access forms, today you will see how to replace the small navigation buttons in Microsoft Access forms with large, non-threatening, user-friendly buttons.
Video: Make Microsoft Access forms more user-friendly with big navigation controls
Make Microsoft Access forms more user-friendly with big navigation controls
Adding command buttons for navigation to a form
Switch to Design view
Display the Toolbox if it is not visible by clicking Toolbox
Click Command Button on the toolbox
Follow the steps of the Command Button Wizard
choose the type of button and associated command
change the appearance if required
assign a name to the button (useful if you further programme the form)
This is the form with the two large navigation buttons you created which are more user-frienldy ways to guide users.
