Excel Sample

Excel is such a powerful software where data can be manipulated in so many ways, such as VBA programming and Macros, Power Pivot, Power BI, Slicers, and of course PivotTables. The following is a brief example of how a PivotTable can work for you. Although I have been called an "wizard" with Excel, I know there is so much more to learn. I am experienced with all aspects of Excel mentioned above: VBA, Macros, Power Pivot, Power BI, and Slicers. Contact me today to learn what I can do for your business' data.

EXCEL SOLUTIONS

Formatting Date & Time As an Unusual Format

I recently was challenged to display the time in an Excel spreadsheet, using a 12-hour format, but without showing the AM/PM portion of the time. Instead, just showing 3:30 instead of 3:30 PM.

If you've ever formatted a cell with time, you know that adding "AM/PM" to the end of the format will result in changing from a 24-hour clock (15:30) to a 12-hour clock (3:30 PM). However, we don't want to see the PM in our solution. But if you don't include "AM/PM" in the formula, it will return the date as 15:30 instead of 3:30. So, how do we get around this?

In my opinion, the easiest way to achieve this is to include the AM or PM, and remove it using the SUBSTITUTE function. Assuming the current time is 15:30, If you were to use the following formula,

=TEXT(NOW(),"h:mm am/pm") the result would be 3:30 PM.

However, by adding the SUBSTITUTE function to our formula, we can remove the AM or PM...

=SUBSTITUTE(TEXT(SUBSTITUTE(TEXT(NOW(),"h:mm am/pm")," AM",""),"h:mm am/pm"), "PM","")

This will replace all instances of AM or PM with empty text.

SUBSTITUTE is used twice, once to remove of AM, and the other to remove PM

Keep in mind that the default Excel formula uses uppercase letters for

AM and PM, so your substitution code must also use uppercase if you want

to see them replaced. The code will find AM, but not am.

Also, the formatting has a space between the time and AM (or PM), so if you want to see that space removed as well, you need to add it to your substitution formula. One more thing to note, if you use one h in the formula, it will return 3:30. However, two h's in the formula would return 03:30. It's just a matter of preference, one or two number spaces for the hour.

If you have a better solution or have an Excel problem you'd like

to see solved, feel free to contact me, and I will add it to this page.

Excel PivotTable Example

Lets say we are given a simple set of data and want to create a PivotTable. Here's how you do it.

From the "Insert" tab, click on Tables, then click PivotTables.

Click inside the Table Range box, the highlight the entire table. Next, in this case, we are going to build our PivotTable in the existing sheet. Click "Existing Worksheet" and then click on the cell where you want to start your table. In this case, F1.

After you press "OK", you will see a blank PivotTable template. On the right side of the screen, you will see a list of columns from your table, and four boxes where you will create your Pivot Table.

First, we want this table to show us the sales from each region, so we are going to drag Sales into the Values box.

Next, we are going to drag Region into the Rows box. You can now see your PivotTable beginning to take shape.

Excel Sample

EXCEL SOLUTIONS

Formatting Date & Time As an Unusual Format

Excel PivotTable Example

This website uses cookies.