Scale to Fit Excel Spreadsheet to Print One Page

The Page Layout tab in Excel 2007, 2010, and 2013 has a scale to fit group. Change the Width to equal 1 page and the Height to equal 1 page from the drop down lists.

scale to fit group

After you change it to 1 page for the width and the height you will notice the scale changes from 100% to a lower amount if it necessary.

excel scale to fit 1 page

I think that anything over 70% should work. Lower values will obliviously make the font smaller when printed.

 

 

Posted in Excel | 3 Comments

Comparison Operators Cheat Sheet

Comparison Operators in Excel

Posted in Access, Excel | Tagged | 1 Comment

Create a Countdown Timer With Dates in Excel

This post will share how to create a date countdown timer in Excel. The final result will result in putting together multiple formulas to show how many days, hours, minutes, and seconds there are leading up to a future event.

First I type some labels in A1 through A4

A1 = “Today’s Date and Time”

A2 = “Hour”

A3 = “Minute”

A4= “Second”

I enter formulas in column B.

B1 =NOW()

B2=HOUR(B1)

B3=MINUTE(B1)

B4=SECOND(B1)

now function

The NOW function returns the current date and time. Pressing the F9 button key will update the value.

The HOUR function returns the current hour expressed in military time. Zero is 12:00AM and 23 is 11:PM.

Hour function

The MINUTE and SECOND formulas return a value between 0 and 59.

I create a custom format on the NOW function result in B1 to have AM/PM displayed.

am pm excel custom format

Then I enter some labels in D1 through D4 and values in cells E1 through E4

Event Date – 12/20/2016 8:00 PM

Event Hour – 20

Event Minute- 59.9999

Event Second – 59.9999

More labels in cells G1 through G4 and formulas in cells H1 through H4.

Days =INT(E1-B1)

Hour =IF(E2>=B2,E2-B2,24-B2+E2)

Minute =INT(E3-B3)

Second =INT(E4-B4)

event date

I  create an IF function that will determine if the event hour is greater than or equal to the current hour. If true, then event hour – current hour. If false, then =24-current hour+event hour. Times are in military 0 through 23.

if statement hours measured

The INT function rounds down the number to the nearest integer.

I create range names in my individual values from H1 through H4.

range names

Now I can see how many days, hours, minutes, and seconds are left from now till the event date on 12/20/2016 at 8:0o PM. This is all with subtraction formulas in  cells H1 through H4.

I finish up with creating a formula that concatenates the values into a string text output.

The & Ampersand is used to join text strings. I use the Range names in the formula in between the labels.

Final result:

756 days 23 hours 31 minutes 33 seconds left

Download my example spreadsheet  Date Countdown Timer.

 

Posted in Excel | Tagged | Leave a comment

How to use an Input Box in Excel

In this post, I’ll share how to collect data in an Excel spreadsheet using an Input Box. I’ll try to make it as easy as possible with lots of screenshots. The purpose of my example is to have someone click a button in Excel that collects data. That “input box”  will lead to a box that asks them to enter units and then a price value. After inputting those values, Excel will perform VBA code that will automatically enter the number of units in B1, the price value in currency in B2 and a formula result in B3.

Let’s get started. Using Excel’s Developer tab, I click the Insert menu then Command Button(Active X Control).

insert command button active x control developer tab excel

Then I left click and drag the shape and location of this command button.

command button

I then right click the command button and click View Code. This takes me to the Visual Basic editor in which I can create the VBA code to make all this happen. Switching between Excel and the code window can use the keyboard shortcut AlT+F11.

Active X command button view code right click

With the insertion point between the Private Sub CommandButton1() and End Sub lines I create variable declarations by typing:
Dim Units as Integer
Dim Price as Currency
Dim Total as Currency

Dim stands for dimension. It is used to create space or dimensions of allocated memory. It helps set up declaring a variable.
Dim declarations of varables

Now I’m going to set up an expression that will input the value of the Units.
I type
Units = InputBox(“How many units did you sell?”)
Range(“B1″).Value = Units

The user will get a prompt to enter units and then the variable I declared (Dim) is going to store that value in memory and enter it in cell B1.

Units command Button screenshot

Whatever the user enters in the InputBox will immediately go in cell B1. Also have to enter a value or else a null value will cause the code to debug.
Units input box Excel

Now I type the Price input prompt I want from the user.
Price = InputBox(“What Price?”)
Range(“B2″).Value = Price

Price = INputBox Excel what price

And this is what happens in Excel after for this portion of the code. User enter a price and the price is then entered automatically in cell B2.

Price Input Button Excel screenshot

Price Input Button Excel

The last line of code I type is a formula result.

Range(“B3″) = Units * Price
Command Button Total

I then create a caption for the Command Button by clicking on the properties button (from the Developer Tab) and type a caption I want the button to have. I need to make sure I’m in Design Mode to do this or else it will just want to run the macro.
Command Button Caption

Download my Excel keyboard shortcuts here. And if you like this post, make sure you sign up to receive others by joining my mailing list.

Posted in Excel | Tagged | Leave a comment

Access Input Mask Cheat Sheet

Use an Input Mask to control data entry in an Access database. For example, you can force users to enters  a phone number with an area code or an employee identification code with 2 letters followed by 3 numbers.

Below is a cheat sheet for Access input masks.

Access cheat sheet input mask

(000)-000-0000 will force the user to enter a phone number with an area code.

(999)000-0000! will force to user to enter a 7 digit phone number but is optional for the area code. The exclamation mark causes the input mask read the numbers from left to right.

LL-000 will force the user to enter 2 letters followed by 3 numbers.

>L<??????????????? will make the first letter capitalized and the following letters lower case.

Check out my Access keyboard shortcuts guide or other Access blog posts by clicking on the Access link on the right sidebar.

Posted in Access | Tagged , | 2 Comments

How to use Countif and Countifs Functions in Excel for Baseball

This blog post I will share how to use the Countif and Countifs formulas.

Let me use some baseball stats of my favorite team, the Reds.

raw data

The easiest thing to do with the raw data is to create range names off the selection. After I select the data including the header row, I click the formulas tab–>create from selection. This steps creates range names in columns.

Create from selection Excel

If you are not sure about using range names in Excel, check out my blog post tutorial on why you want to use range names if you are not doing so.

Now we are ready for the countif function. I will use the countif function to count how many times the Reds won for the entire winner column. The countif looks at a column of data and adds a count for each condition you specify.

countif excel

The countif function looks at the Winner range and counts each time “Reds” appears within the range. Since “Reds” is text and not a number data type, I need to have quotes around the text string.

The countifs function is just like the countif function except that it can use multiple criterion. In this example I want to count how many times the Reds won while playing at home in Great American Ballpark.

countifs

My final result.

Excel final formulas

And yes, there is always “next year.”

Posted in Excel | Tagged | 2 Comments

How to Use Excel Traffic Lights with Conditional Formatting Dates

In this post, I’ll share how to set up traffic lights in Excel to show variances between a scheduled date and the actual date. This post in response to a comment Stephen wrote from an earlier post I wrote called How to format dates that are over 1 year old to turn red in Excel using conditional formatting.

Stephen’s comment:

Hi I am trying to create a spreadsheet using traffic lights in (2010) to show when a document review date has expired. One column will show the actual date of review, the next will shown the scheduled review date and the next column will contain the traffic lights,

green if carried out before or on the scheduled review date,
Amber if between 1 and 30 days overdue and
red if over 30 days overdue.

At the time of review the actual date column will then be updated to reflect the new date . The scheduled review column will also be updated to show next expected review date. Can you advise me best way to handle this please.

I would arrange the spreadsheet as seen below.

xcel formulas with dates actual minus scheduled variances

Then I would create a formula that subtracts the actual date from the scheduled date. For example =C2-B2 would be -36 days in D2 cell because the actual date happened 36 days before he scheduled date. If the scheduled date and actual date were the same the formula value would be zero. And if the actual date was after the scheduled date it would display in how many days.

Then select all the cells you want the conditional formatting rule of traffic lights on. Then click the Home tab–>Conditional Formatting–>New Rule.

Conditional Formatting New Rule

Make the following adjustments:

  1. Change the drop down in Format Style to Icon Sets.
  2. Select the Icon Style to be the traffic lights.
  3. Click Reverse Order
  4. Red traffic light is when value is greater than or equal to 30 number.
  5. Yellow traffic light is when less than 30 and greater than or equal to 1 number.
  6. Green traffic light is when less than 1 number.

Icon Sets Rules Manager Excel

At this point, you will see the traffic lights and the values of the formulas in column D. So the trick is to make the font change to white.

Change font to white trick

And the final result:

Traffic lights Excel Conditional Formatting

Download my example file to practice with at this link traffic lights conditional formatting.

Posted in Excel | Tagged | Leave a comment