Happy January!
I am looking forward to getting back into doing instructional posts as well as the launch of several more Excel templates. I will be doing more vlogs soon and will post links to them here.
Since my last post my 4th son was born. Ezra was born at home with the help of our midwife. It was a wonderful delivery and my wife did a tremendous job. The other 3 boys are growing up fast. Before long wrestling with my kids will be a losing proposition.
The family also went through a big move and now we are living in the country and have moved the company to this location as well. It was a great first year for our second generation construction business. If you would like more information about that you can find it at www.brasbyconstruction.com
I have reworked several of my Excel templates for the business. My overtime calculator instead of being straight prorated now takes into account how many days were worked during the week and prioritizes giving each day an equal amount of regular time. This way of calculating does a better job giving longer days the proper amount of overtime. The calculator still can handle multiple jobs in a day, short days, as well as Work Comp splits.
I am also working on adding work comp premiums to my Job Profit and Loss template. Since my company has multiple rates that can apply to the same job I will be able to calculate the cost of these premiums in the Job P&L. At this time I am not planning on selling this template, but I may feature it in an upcoming vlog.
This year I also purchased Office 365 so my blogs and templates will no longer be in Excel 2010. I used Excel 2013 for a couple months and just recently updated to Excel 2016. The biggest differences seem to be in appearance to mirror the updates in Windows 8 and 10. I have enjoyed the little box at the top that allows you to enter the name of a tool you are trying to find and have the link to it pop up. I also noticed that in PivotTables and PivotCharts they added a Timeline feature that is much like a slicer, but gives lots of options with filtering by date.
Once Quick Books comes out with patches for Windows 10 I will consider getting a laptop with touchscreen tech. It would be handy to be able to scroll left to right easily by touching the screen. I am currently taking financial analysis classes at my local community college. This has been fantastic because it is not a traditional class. The professor comes to my place of business and helps me with my set of books while expanding my capabilities. I am about to start my 3rd semester in this certificate.
I am currently building an activity tracker. It will allow the user to track how they spend their time and will generate reports that include trends based on weekdays and months. I am working on adding a goals feature to it that should allow the user to set goals based on day, week, month, or the entire year and know how to set short-term goals to reach their long-term ones. It should also be able to track progress toward each goal.
As always I am open to ideas for new templates as well as any feedback on any of my content. Until next time, utilize the tools that you have and remember that every cell = a calculator.
Nathan
-ExcelHelpsYou
Monday, January 18, 2016
Monday, March 30, 2015
Product Release - Overtime Calculator
Overtime Calculator
Weekly & Bi-Weekly Time Cards
Designed to make entering time into Quick Books easier, this calculator prorates all time over 40 hours for every line item. This makes job costing more accurate and eliminates doing math.
I recently built this for my own use in my construction business. Because my employees are often on more then one job in a day or have days that aren't 8 hours long, I can't just do 8 hours of regular time and the rest overtime. Quick Books does not calculate overtime. That is why I built this calculator.
Here you can see a sample of what the Time Card looks like. You can change the title to include your business name. My business uses job numbers to track divisions and separate multiple jobs for the same customer.
When using the weekly time sheet in Quick Books, the days are laid out horizontally with the initial of the weekday with the day of the month. Then all times for that day are listed vertically with a total time at the bottom.
The layout of this spreadsheet makes it easy to tell what day of the week each line is as well as what the totals for each day are. You can see how it is calculating the overtime in the weekday totals.
When important, throughout the document instructional boxes will appear to guide you. If their location is not ideal for you, simply click and drag them to where you want them.
In this calculator lunch is deducted from the total time. So, if your company offers paid lunches, simply do not enter any time in this column.
The total hours calculates to the minute. Quick Books can handle paychecks being accurate to the minute. When entering time into Quick Books, use a colon (:) to separate hours and minutes if you are entering to the minute. If you use a decimal (.) then Quick Books converts that decimals into minutes. So, .5 = 0:30
For the weekly time card, the gross pay details are in the lower left hand corner. Enter the hourly rate you are paying your employee and the overtime rate and gross pay calculate automatically. This is a great double-check before you print the paychecks to ensure your data entry is correct.
You can overwrite the formula here if your overtime is calculated differently.
Note: Be sure that your time is correct when entered into Excel before entering into Quick Books. Any changes in Excel changes every number as the ratios have changed. If you have already entered into Quick Books before the mistake is found, all of those numbers need updated.
For my business, I built my payroll report to handle up to 7 employees for 2 weeks and up to 5 workman's comp classifications. If you are interested in having this calculator custom built to match your company, please message me for a quote.
I have provided blank time cards for your employees to fill out that match the design of the weekly time card. Use it if it would be helpful to you.
To see this spreadsheet in action click HERE
For whatever reason Etsy is not allowing Excel files to be uploaded right now, so I have this file for sale on Ebay. You can find it HERE
Thanks for checking it out. If you know someone who could use this, pass it along and don't forget to Follow By Email on the right.
Next week we will be looking at Tools - Styles Group
Nathan
Every Cell = A Calculator!
NERD ALERT!
For those of you who would be interested, I wanted to share a difficulty I came across when developing this product. When Excel calculates time it converts it into a decimal of a day. So, when I started taking my time that was to the full minute and dividing by a percentage of the week, Excel did just that. Only problem was that it was accurate in its math to the second.
It wasn't until I started imputing time into Quick Books that I discovered it was doing this. Because my time format was to the minute, Excel was rounding and my time was off 2 or 3 minutes in every week.
My solution was to use the FLOOR Function to force Excel to stay at full minutes. Now the problem was I had a few minutes that had to be added back in to get to 40 Hours. Using an array SUM inside of an IF function,
I had the spreadsheet add a minute to regular time early in the week until it reached 40 hours and then later in the week to add the minute to overtime. It only adds the minute if that day was short 1 minute after the calculations.
It wasn't until I started imputing time into Quick Books that I discovered it was doing this. Because my time format was to the minute, Excel was rounding and my time was off 2 or 3 minutes in every week.
My solution was to use the FLOOR Function to force Excel to stay at full minutes. Now the problem was I had a few minutes that had to be added back in to get to 40 Hours. Using an array SUM inside of an IF function,
Regardless of if you followed that, know that it is working and that the daily totals help you confirm it is working when you compare it to Quick Books.The same is true of confirming the Gross Pay. Thanks and I will see you next week.
Monday, March 23, 2015
Special Announcement!
Special Announcement!
A Personal Post
This week I am taking a quick break from Excel content to
bring you a personal post. This will be
the case in every month that has five Mondays.
This week I want to tell you about a special trip I took and why.
My 10th anniversary is in September. Usually this would mean a long weekend away
to celebrate with my wife, in September. This year we took that trip in March,
because in September Valerie will be 8 months pregnant! This will be our fourth
child and a chance at our first daughter.
We just got home from a great weekend at the Chipeta Solar
Spring Resort in Ridgway, CO. I got to
go snowboarding while Valerie went window shopping in Telluride. Now, before
you accuse me of abandoning my wife and not letting her actual go shopping let
me explain.
Because Valerie is 10 weeks pregnant, our midwife and doctor
recommended that Valerie not go snowboarding.
So Valerie was able to go explore Telluride while I hit the slopes.
Having been married for almost 10 years now, and being graduates (twice) of
Dave Ramsey’s Financial Peace University, we discuss big purchases and have a budget
for everything else anyways.
With a day to explore the town of Telluride, Valerie rode
the free gondola from the ski resort into town. Cash in hand, Valerie walked
the streets and was blown away by the outrageous prices of simple items. A
sweatshirt hanging on a rack on the sidewalk had a sticker price of $450! We
met up for pizza at the base of the mountain and had a good laugh at the ‘inflation’
that has hit these remote mountain towns.
The weekend was filled with beautiful scenic drives and
walks up and down main streets of largely deserted towns as this is their slow
season between winter and summer. This was very much a picture of our weekend
without children that was a wonderfully quiet time before a busy summer of
soccer and preparing for our newest member of the family.
Next week the Excel content will resume with a product
release of an overtime calculator designed to make data entry into QuickBooks
easier. Leave a comment as to which gender you think our baby will be!
Monday, March 16, 2015
Shortcuts - AutoFill Options
Autofill Options
There is a way to exponentially increase your speed in Excel. If you use dates, days of the week, patterns or lots of formulas then AutoFill will transform the way you enter data into Excel.
This post is going to take a lot of pictures to explain the process thoroughly. It will be best demonstrated in this VIDEO if you have 10 minutes to see it in action.
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Fun Fact: Excel limits its calculations in relation to time to the year 9999.
 |
 |
Introduction to Absolute Referencing
 |
 |
 |
Absolute referencing can be toggled through the options using the F4 key while entering a formula. More detail to come on this in a later tutorial.
Bonus: Customize Status Bar
 |
 |
This will give you the ability to analyze your data very quickly without entering formulas. Simply select the data you want to analyze and look at the status bar.
If you have learned something today and want to learn more, follow me by email to receive future posts right to your inbox. I only post once a week, so you won't be flooded with unwanted emails.
Enjoy your week and God Bless!
Nathan
Every cell = a calculator!
Monday, March 9, 2015
Introduction to Formulas
Introduction to Formulas - Logical Arguments
Backbones of Formulas
In this post you will learn the symbols that Excel uses to do math. Every cell in Excel is a calculator. If you just type data into a cell, that is what is displayed. If you start with the equals sign (=), then Excel will calculate whatever formula you insert.
A formula is simply anything that requires calculation. A function is a way to tell Excel how to do a calculation within a formula. We will cover functions at a later time.
In Excel every cell has a name or a reference. When a cell is selected, its name is displayed in the Name Box.
Here we can see that cell C3 is selected. Every cell has a default name that is the Column letter followed by the Row number.
When you select a cell, the contents of that cell are displayed in the Formula Bar.
Here we see that cell D4 contains the formula =1+2. The cell displays the result of that formula which is 3.
By putting a formula in the cell, it became a calculator. It will retain the formula, so that at a later time if you want to know how you got to the result you can still see the calculation. If you used a calculator on your desk to do this calculation, you can't tell if you made a mistake because the data you used would be gone.
This type of formula is static; it will not change until you select the cell and change the formula.
If you are referencing data in your workbook in your formula, then use cell referencing to make your formula dynamic.
In cell E4 is the formula =C3+C4. This will add the contents of these two cells and display the result. Now when you change the contents of either of those cells, the formula will recalculate.
By changing the contents of cell C3 to 5, the result of the formula =C3+C4 is now 7. This is a very powerful concept to understand. This can transform a standard spreadsheet into a program that will do the calculations you want.
Here are the symbols for the different logical arguments in Excel as well as sample formulas using those symbols.
 |
| Equals sign |
This may seem too simplistic, but I use this a lot. You can put a formula in the cell that you are referencing and only bring the result of that formula into the new cell. This also works on cells that contain text.
Now that you understand the structure of a formula, here is a list of the different symbols you can use to build a formula.
 |
| Addition and Subtraction Symbols |
The addition symbol is left of backspace and above enter on the number pad.
The subtraction symbol is two keys left of backspace and two keys above enter on the number pad.
 |
| Multiplication and Division Symbols |
Multiplication is the star symbol above the 8 on your keyboard and above the 9 on the number pad.
Division is the forward slash left of shift and above the 8 on the number pad.
The next two pictures demonstrate comparisons that will yield either True or False.
 |
| Less Than and Greater Than Symbols |
Less than is the left carrot above comma.
Greater than is the right carrot above period.
 |
| Dual Symbol Arguments |
These are a combination of two of the above symbols.
 |
| Exponent Symbol |
This symbol can be found on your keyboard above the number 6. When used in a formula the number after the up carrot is the exponent.
Because I have been repeatedly telling you that every cell is a calculator, I wanted to show you how many cells are in every Excel worksheet.
 |
| The Last Cell in Excel |
There are over 17 Billion cells in every Excel worksheet because some people reached the last cell in Excel 2003 and requested more. Microsoft went overboard to help ensure that this problem wouldn't happen again.
Good luck practicing these new words as you learn the language of Excel.
Click-through to the Vlog version of this episode on YouTube HERE
Next week we will look at the Autofill Shortcut and the Status Bar.
See you next Monday,
Nathan
-Every cell = a calculator!
Monday, March 2, 2015
Clipboard Group
Clipboard Group
Power hiding in plain sight
Have you ever wondered what the paste symbol is? It is a Clipboard. Most of the time this feature collects data onto the Clipboard in the background, but you can also access the Clipboard on the Ribbon in the Home Tab: Clipboard Group.
By clicking in the lower right-hand corner of the Clipboard Group, you can open the Clipboard Task Pane.
Collection on the Clipboard
Normal copying and pasting does not collect multiple sources of data to be pasted at a later time. Using the Clipboard allows collection of up to 24 different data sources. Data can be collected onto the office Clipboard by Cutting or Copying.
Cutting removes the data from the source. The Cut command looks like a pair of scissors. It is in the Clipboard group and can be pinned to the Quick Access Toolbar (QAT). The shortcut for this command is Ctrl+X or drag-and-drop.
Copying retains the data at the source while collecting the data on the Clipboard. The Copy command looks like two pieces of paper. It is also in the Clipboard group and can be pinned to the QAT; the shortcut is Crtl+C.
In this example the Clipboard has two data sources collected. The products in this example are fruits and vegetables. Each product needs assigned a type so that you can tell Excel what to do with each type.
From the Clipboard Task Pane you can click each cell in Column C and then paste one at a time either fruit or vegetable. This would be faster then typing Fruit or Vegetable in each cell, especially if you had 50 different products.
At the bottom of the Clipboard Task Pane is an options menu that lets you choose when and how the Clipboard acts while it is hidden.
To paste from the Clipboard, either click on the item on the Clipboard, or click to the right of the item in the blue box to open the drop-down menu, then click paste.
The Paste shortcut Ctrl+V will paste the last data point that was pasted.
To remove an item from the Clipboard, open the drop-down menu for that item and click delete. If you are done with everything on the Clipboard, then click Clear All at the top of the Clipboard Task Pane.
Drag-and-Drop Cutting
Easily move data around your workbook by drag-and-drop. Select what you would like to move. Click-and-hold on the border of your selection, then drag it to where you want it.
If you want to drag it onto another sheet of your workbook, drag it onto the tab for that sheet and press Alt.
This is what your cursor will look like when you are ready to move an object in Office.
Format Painter
This is a powerful tool that looks like a paintbrush. It copies a cell's formatting and is then pasted in the next cell or group of cells that are selected. To paste in multiple different cells, double-click the format painter tool while the cell with the formatting you want is selected.
Because the cells containing Fruit and Vegetable we pasted earlier had thick borders, that formatting pasted as well. By selecting a cell with the formatting we want, in this case cell E17, we can copy just the formatting and not the formula that is displayed in the formula bar.
Paste Options
Normal Paste
To paste, select the cell that will be the upper left corner of any data to be pasted.
Normal pasting brings formatting, formulas, and raw data from the source. It does not adjust column widths.
Paste: No Borders
This option is real straight forward. It does everything normal paste does except it elimanates all borders.
Paste: Keep Source Column Widths
This command can be very helpful if you have taken the time to make the column widths at the source just like you want them.
Paste: Transpose
As a reminder, the columns in Excel are vertical and have letters as headings. The rows are horizontal and have numbers as headings.
Often when organizing data into a blank worksheet, I change my mind halfway through as to how I want to lay out the columns and rows. Paste Transpose will give a quick snapshot of what it will look like to flip your data. Now the columns and rows have switched.
Usually you will want to have the categories with the most unique data be your column headings because it is easier to scroll down through your data instead of sideways.
Paste: Values
This paste option allows you to take the result of any formulas you have used to manipulate your data and paste only the values in the destination cells. Note: Any changes to the cells referenced in the formulas at the source will not cause the destination cells to update, only the values have been pasted.
Paste: Formatting
Very similar to Format Painter, this tool pastes only the formatting. The destination cells retain the data they contained before the paste.
The main difference between Format Painter and Paste Formatting is the size of the source data.
With Format Painter the source can be smaller than the destination cells.
With Paste Formatting both the source and destination cells will cover the same number of cells.
Paste: Options
After pasting and before you do anything else, if you don't like how your data pasted you can change which paste option you want.
Open these options by clicking on the Paste Options box that appears in the lower right-hand corner of your data after pasting. Choose the option that works best for you.
To see these tools in action, click-through to the vlog version HERE.
I hope this has been helpful for you. It may take some practice to learn the language of Excel and choose the right tool for the job the first time.
I'm looking forward to doing more in-depth tutorials. Next week we will look at some basic logical arguments and how to write a formula.
Until then,
Nathan
-Every cell = a calculator!
Subscribe to:
Posts (Atom)