Using OpenAI's ChatGPT to Update My Year in Pixels Excel Template

I've been seeing more and more videos on TikTok about this mysterious new artificial intelligence program called ChatGPT. People are saying it will eventually replace Google search. But what really got my attention was a demonstration of ChatGPT writing VBA macros for Microsoft Excel. Now I knew I had to try it for myself. 

I made a Year in Pixels Excel template to track my daily mood back in 2018 but haven't updated it lately. Since it's the start of a new year, I recently received some new comments and questions about how to use and modify the spreadsheet so I figured now would be a great time to update it and add a few new features. Then I had an idea - why don't I use this opportunity to update my mood tracking Excel template to try out ChatGPT? Let's give it a go!

How to have cell fill color conditional formatting change based on the color of another cell?

The request from a user that got me thinking was "how to easily change the colors in my year in pixels sheet?" An experienced Excel user could probably ascertain the colors are applied by conditional formatting formula rules. However, for users who are not Excel savvy, it's not very obvious and there should be an easier way to change the colors. As far as I know, there is none, or at least no easy way in my current version of Excel, to have the fill color in the conditional formatting reference another cell's color. So instead, I will have a macro update the conditional formatting rules at the click of a button. Let's jump over to ChatGPT.

How does ChatGPT work?

Here's the answer after asking it directly: 

GPT (Generative Pre-trained Transformer) is a type of language model developed by OpenAI that uses a transformer neural network architecture to generate text. GPT is trained on a large dataset of text and uses this training to generate new text that is similar in style and content to the training data.

ChatGPT is a variant of GPT that has been fine-tuned on a specific task, such as having conversations with humans. To fine-tune GPT for this task, ChatGPT is trained on a large dataset of conversation data and learns to predict the next word in a conversation based on the words that came before it.

When you input a message to ChatGPT, it processes the message and uses its knowledge of language and conversation patterns to generate a response. It does this by predicting the next word in the conversation based on the input it has received and the context of the conversation so far. The output of ChatGPT is then a response that is generated based on this prediction.

One thing to keep in mind right off the bat: ChatGPT is not always right. It says it right there on the main page under limitations: may occasionally generate incorrect information; may occasionally produce harmful instructions.

For example, I asked "list the ten tallest roller coasters in the world" which should be fairly easy and instead it produces a list that is incorrect and doesn't even make sense.

Not a great start but let's see how it does creating a macro from scratch.

Creating Custom VBA Macros Automatically with ChatGPT

I wasn't sure how specific I needed to be so I decided to err on the side of caution, plus I thought the more detailed I am the less modification I'll probably need to do later. Now I ask ChatGPT: "create a vba macro to make a new conditional formatting rule where range is C4 to N34, if cell value = 5 then change the fill color to the same color as in cell P12." Then it began typing and my jaw dropped.

I copied and pasted the code into Excel VBA editor and it worked! One thing I forgot is this macro will just add new rules applied to the same range. I need to delete all the existing formatting rules before adding new ones, so I ask ChapGPT:

I added the above code to the earlier response and linked it to a newly added button on my sheet. Now a user can change the fill color of a cell, click the button, and the fill colors update automatically to match.

If you want to see all this play out in real time, watch the video I recorded below:

Thoughts on the Future of ChatGPT

It's awesome that it doesn't just spit out the code, but it also suggests how you might need to modify it AND tells you how to run the macro as well. Even though I've only asked it to make simple macros, I already see how this program could save a lot of time.

I'm not done experimenting but so far ChatGPT seems like a much better option over Recoding macros or Google searches that might take you a few tries to find exactly what you're looking for. Especially when you can get custom code on the fly. On one hand, I feel a little obsolete, but on the other I also don't think ChatGPT will completely be replacing programmers just yet as you can see I still had to understand the code and modify it to fit my exact needs.

Download my Year in Pixels template for free here and try it for yourself. Open the macro editor to view the final codes written by ChatGPT.

2015 NFL Helmet Schedule Spreadsheet

The 2015 NFL season is just around the corner! The inaugural Hall of Fame game will take place in Canton, Ohio on Sunday, August 9^th. Four weeks of preseason games will follow before the start of the regular season on Thursday, September 10^th.  For the complete 2015 NFL schedule, download my free spreadsheet that includes all NFL helmets from every team. It’s a fun way to look at your favorite team’s opponents.

Even though basing a team's supposed “ease of schedule” on the previous year's record is a faulty premise, it's still fun to do and happens all the time. Once again, I’m not holding out much hope for my Cleveland Browns. They still haven’t found their franchise quarterback. I think the NFL season is even harder to predict than college football. Teams at the bottom one year can go right to the top the next. That’s why we love it!

As for the actual spreadsheet itself, it’s very simple at this point, and proves not every Excel file has to have macros or conditional formatting. There are no major formulas or tricks, just images of each of the football helmets. In the future, I am planning on linking the helmets to each team which will make updating the schedule for next year much easier. And I’m always open to suggestions for improvement.

Download the football helmet schedule using the link below.

2015 NFL Helmet Schedule Spreadsheet.xls download 

Yes, it takes quite a long time to assemble all the NFL helmet logos each and every season but it’s a fun way to look at the schedule and all NFL helmets at the same time. Luckily, the NFL doesn't see as much change year to year as college football does with their constantly changing conferences.

In the comments below let me know what you think about your favorite team’s chances this year!

20 Excel Shortcuts You Need to Know. Number 15 is My Favorite.

Excel is a powerful tool, but you can’t call yourself a power user until you've mastered the essential keyboard shortcuts. I've mostly avoided the obvious and essential shortcuts that also work in other apps (such as Ctrl+Z for undo and Ctrl+C for copy, Ctrl+B for bold, etc.) but besides those here are the 20 Excel shortcuts you need to know:

1. F1: Access the Excel help file
Press F1 to access the Excel help file. Excel has hundreds of keyboard shortcuts so one of the most useful features of the help file is to search for ‘keyboard shortcuts.’ You'll find the full list of shortcuts there but the 20 listed here are the ones you’ll keep returning to.

2. Ctrl+`: Show or hide formulas
Not sure which formulas are running in your spreadsheet? Use Ctrl+` (the accent key, to the left of the number 1 key) to see the formulas in the cells rather than their results. 

 

3. Alt: Access the ribbon
Every single Ribbon command in Excel can be accessed via the keyboard. Hit Alt and you’ll see a letter (or a two-letter combination) above each ribbon tab. Type that letter or combo to use it. I've added the camera to the ribbon to quickly take screenshots, using Alt+4, as shown below:

4. Ctrl+;: Enter the current date
Using Ctrl+; saves time checking and entering the date. I find myself using this a lot. Please note this is a fixed date and not the =TODAY() function.

5. Ctrl+PgUp/PgDn: Navigate between worksheets
Complex Excel spreadsheets often have multiple worksheets. Rather than clicking on the bottom-of-screen tabs, use Ctrl+PgUp and Ctrl+PgDn to quickly navigate between sheets.

6. Ctrl/Shift+Space: Select an entire row or column
For even more selection power, Ctrl+Space selects an entire column. Shift+Space selects an entire row. You can then use the shift keys plus the arrow keys as appropriate to select additional rows or columns. Remember, C=Column=Crtl.

7. Alt+ =: Sum function
Here is the fastest way to sum your data in Excel: after entering your data in the column, click the first empty cell in that column and enter ALT+= (equals key), then click Enter. It will add up the numbers in all cells above.

8. CRTL+ UP/DOWN: Jump to top or bottom
This tip is particularly useful when you’re dealing with large number of rows. Use this method instead of endless scrolling to save time. Enter CTRL + ↑ (upward arrow key) to jump to the top cell or CTRL +↓(downward arrow key) to jump to the last cell before an empty cell.

9. Alt+Enter: Multiple lines in one cell (line break)
In some cases you may want multiple lines of data or text you typed into a cell to appear on several lines (also called inserting a line break). Instead of entering the text in another cell, press ALT+ENTER. That way you'll start a new line while typing or editing data.

10. CRTL+0/9: Hide columns or rows
To quickly hide a row / rows use CTRL+9. To hide a column / columns use CTRL+0. 

11. F6: Switch between tools
For all those anti-mouse users out there, F6 is the ultimate shortcut. It allows you to switch between the worksheet, the ribbon, task pane, and zoom controls.

12. CRTL + ‘: Copy cell above selected 
If you type Ctrl+' it looks at the cell above the selected cell and copies it into the current cell. 

13. ESC: Cancel changes
Press ESC while you are editing the text or formula in a cell to exit the cell and cancel any changes that you may have made.

14. Shift + F3 : List of functions
Not sure if you need to use a SUMIF or COUNTIF? You know there’s probably a function for what you need to do but can’t remember the name? Open the list of available functions using Shift+F3. 

 

15. Crtl+g; Create bookmarks and other
Use Ctrl+g > Special to do things like Select all cells with comments, Select all cells containing formulas, etc. You can also create a bookmark within a large spreadsheet by naming a cell. Then use CTRL+g to quickly navigate to that cell.

 

 

16. CRTL+1: Format cells
Do you constantly find yourself formatting a cell by changing the font border and fill? Use CRTl+1 to display the Format Cells dialog box. 

 

17. F4: Toggle a reference
Use F4 to toggle a reference between A1, $A$1, A$1 and $A1 while editing a formula (When NOT editing a formula, F4 is an alternative to Ctrl+y which is "repeat" or "redo").

 

18. CRTL+ALT+SHIFT+F9: Update formulas
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. Very useful when you have user defined functions. 

19. CTRL+SHIFT+~: Change date to number format
When Excel automatically formats a number as a date, you can change it back to a number using the shortcut CTRL+SHIFT+"~" (or change the date format using CRTL+SHIFT+#).

20. Alt + F11: Open macro editor
One of my personal favorite and most used Excel shortcuts is ALT+F11 to open the macro editor. Some of my most used macros are how to create folders from Excel and combine multiple Excel files.

Here's a quick summary of all the shortcuts that you can print off or Pin for quick reference later:

Learning to use keyboard shortcuts is one of the best ways to increase your productivity with Microsoft Excel. Are there any Excel shortcuts you often use that I’ve failed to list here? Please let me know!

How to Embed Excel in a Website

One thing I wanted to learn when I created this blog was how to embed Excel spreadsheets into a website. Thanks to free file sharing services like www.box.com embeding files into websites has become a very simple process (and see why I highly recommend you create a website). The very first thing you need to do is to go and register an account at a file sharing website. I use Box.com because it’s free and has many unique features, like keeping stats of how many times your spreadsheets are viewed or downloaded. Once you have an account, follow these five easy steps once you have your Excel spreadsheet ready to upload:

 

Step 1: Create a folder

This folder is where you will upload your spreadsheet. OK, you actually don’t have to do this step but it helps to keep your files organized which is why I recommend you do it.

Step 2: Upload your spreadsheet to your folder
Go to your folder then upload your spreadsheet to by clicking Upload.

Step 3: Copy the markup

When the file is done uploading click the down arrow, scroll down to Share, then click Embed File in Your Site. Now you may adjust the width, height, and color as well as setting other options like allowing printing or downloading. When ready, click Copy to Clickboard to grab the embed code (or markup).

Step 4: Go to your blog

The markup you copied is supported in many web authoring environments and blog services. Go to your blog editor, begin writing your post, then switch to HTML editing.

Step 5: Paste and post

Make sure the markup you copied is the most recent item in your Clipboard and press CTRL + V. You’ve just embedded an Excel spreadsheet into your website! See the example below:



Concluding Thoughts

There are other file sharing services you can use to embed a spreadsheet, like Microsoft’s SkyDrive and CometDocs, but I have had a great experience with Box.

-Nick
Spreadsheet Embedder

Quick Tips: Yes/No Drop Down List

Today I'm going to show you how to make a quick and easy multiple option drop down list in Microsoft Excel 2007 (works in other versions too). Drop down lists are useful when you want to limit a user's entries to only values that you specify. I've showed you earlier how to create a drop down list using Named Ranges, which takes a few extra steps but offers more repeatability. Now I will show you a much faster and simpler solution for creating these types of lists in Excel. 

 

 

First, begin by selecting the cell you would like to add the selectable list to. Next, go to the Data tab on top and select Data Validation. A window will popup. Here you will find from the list of pull down options the ability to change the Allow: feature to List. Now, under the Source type in the name of the values you want to appear in the pull down menu, which each term separated by a comma. Click OK when you are done. That's it! 

 

 

If you click on the cell you should see an arrow appear on the right hand side of the cell. When you click the arrow you should see the name of the values that you entered in the Source box. You can fill down this cell to other cells you would like to contain the same pull down lists. 

 

 

 

Also, you can convert your user entered Yes or No value into a numeric value for calculations using a simple IF formula,: If the user selects Yes then the value is 1, if No, then 0: 

 

=IF(A1="Yes",1,0) 

 

For more Excel spreadsheets tips please consider joining our newsletter. 

 

Check out some of our recommended tools to learn Excel and how to write VBA macros.

Excel Tips: Skill Assessment Exam Answers

I recently took a Microsoft Excel skills assessment exam. There were a few questions which surprised me. Luckily, I was able to figure them out rather easily. I have decided to share a few of the answers from the skills assessment exam (though not all of them) with you. If you're never going to take an Excel exam then please few these as a few friendly spreadsheet tips.

Q: How do you go to the dialog box in Excel (what is the shortcut to go to the dialog box)? Answer: F5

Q: If you enter a time into Excel, does it default to AM or PM? Answer: AM

Q: How do you display the current time in Excel? Answer: crtl+shift+:

Q: How do you recalculate all formulas in a worksheet (what is the shortcut to recalculate all formulas)? Answer: crtl+alt+F9

Please follow my blog and stay tuned for more!

Some lesser known keyboard shortcuts in Microsoft Excel

Here are a few shortcuts in Microsoft Excel that you may not be familiar with:

Alt+F11:Open VBE
Ctrl+Shift+Enter:Array formula
Ctrl+F3:Define name
F3:Paste name
Ctrl+Spacebar:Select columns
Shift+Spacebar:Select rows
Ctrl+1:Format cells
Ctrl+B:Bold
Ctrl+U:Underline
Ctrl+:Current date
Ctrl+shift+:Current time

File this one under "what are some keyboard shortcuts in excel?"

How do I insert the degree symbol in Microsoft Excel? (And other keyboard shortcuts)

There are a number of keyboard shortcuts that can be used to generate symbols in Excel spreadsheets. The most asked about one is how to insert the degree symbol. Here is a list of some of the most frequently used keyboard shortcuts:

Alt + 0176 = ° (Degrees)
Alt + 0149 = • (Bullet)
Alt + 0162 = ¢
Alt + 0188 = ¼
Alt + 0189 = ½
Alt + 0190 = ¾
Alt + 0177 = ±
Alt + 0178 = ²
Alt + 0179 = ³
Alt + 0163 = £
Alt + 0128 = €
Alt + 0151 = — (m dash)
Alt + 0150 = – (n dash)
Alt + 0187 = »
Alt + 0169 = ©
Alt + 0174 = ®
Alt + 0165 = ¥
Alt + 0177 = ±
Alt + 0247 = ÷
Alt + 0166 = ¦
Alt + 0134 = †
Alt + 0227 = ã
Alt + 0191 = ¿
Alt + 0161 = ¡
Alt + 0209 = Ñ
Alt + 0241 = ñ
Alt + 0225 = á
Alt + 0233 = é
Alt + 0237 = í
Alt + 0243 = ó
Alt + 0250 = ú
Alt + 0252 = ü
Alt + 0186 = ° (1° = primero)
Alt + 0170 = ² (2² = segunda)

As with most software these days, there are a number of different ways to get to the same result. You can also use character map to use the symbol you are looking for:

A character map of all special symbols, including foreign language characters, can be displayed by going to Start>Run, typing charmap, and clicking OK (Windows XP users). Vista users will type charmap into the Search window at the bottom of the Start Menu. Choose the font corresponding to the one you're using and click on a character. Next click Select>Copy to copy the character. Return to your document, click where you want the character inserted, and go to Edit>Paste (or do Ctrl+V) to insert it.

Finally, the simplest way is simply to where MSOffice users can go to Insert>Symbol to accomplish the exact same thing.

How do I highlight duplicate rows in Excel without deleting them?

There is a Delete Duplicates function in Excel that makes it very easy to delete any duplicate data in your spreadsheet. However, sometimes you may not want to delete that data, you just want to know that there are duplicates. One way of doing this is with a simple macro. To highlight and draw attention to duplicates in a Microsoft Excel spreadsheet with a bold red color create this macro (alt+F11 opens the macro editor):

Sub DupsinRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

The statement "Application.ScreenUpdating = False" prevents the screen from updating to ensure the macro runs faster and the screen will not flicker. Don't forget to set it back to "True".