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.
The Subtotals command in Excel offers a very quick of getting, well, subtotals – sums, averages and more- without the use of functions and formulas.
If you can read these instructions or watch the video, you will know how to calculate subtotals for the hugest, most humongous lists. But you must sort the table or database first. I said, you must sort the table. That’s right, don’t forget to sort first.
Video: Super easy subtotals in a Microsoft Excel database
Super easy subtotals in a Microsoft Excel database
Step 1 – Sort the database
Make sure you have a database first and then if you want to have a total by Region, sort by Region.
As long as you have no blank rows or blank columns in your list or table, just click inside the column to sort by and then click the Sort command.
Step 2 – Subtotals
Click any cell within your database and select Subtotals from the Data menu
Now read carefully the instructions in the dialog box.
Under At each change in make sure that you select the same field name (column heading) as in Step 1.
For Use function choose from the dropdown. Usually, Sum is used but you can easily get an average or other calculation of your choice.
Add subtotal to lets you choose which column(s) you want to add up or average so choose as few or as many as you need.
Click OK to see the result. Notice the outlining on the left hand side.
Step 3 – Outlining
Click the symbols (-, + and the figures 1, 2, 3 to the left hand side of the spreadsheet) to display or hide various levels of details in the database.
Step 4 – Remove All Subtotals
To remove the subtotals, select Subtotals in the Data menu. Click Remove All in the dialog box.
