UFO Dashboard Project

The UFO dashboard project walks you through the process of taking a large data file and turning it into a usable Excel dashboard, complete with graphs and drop-down menu selections. The dashboard shows the number of UFO sightings over time from around the United States.

Start the UFO Excel Dashboard Project

Start by downloading the UFO Excel data file. You can also follow along with the video HERE. You'll turn this raw data in a plain spreadsheet into a nice Excel dashboard.

Look at all this data! Your boss is a UFO hunter and sent you this file of UFO sightings over time across the United States. She wants you to turn this data into a dashboard that will help her identify the best place and time to spot UFOs. Each sighting specifies the shape of the UFO, and she wants recommendations based on shape.

The first thing you determine is that it would be helpful to know the year as well as the day of the week of the sighting. In cell E1 write "Day of the Week". We're going to use the TEXT function to get the day of the week for each date. In cell E2 write the following formula:

=TEXT(A2, "dddd")

The second argument "dddd" tells Excel that you want the result to be the spelled out day of the week. Now how are you going to copy the formula all the way down the worksheet for the rest of the sightings? Move the cursor to D2, then hold control and press the down arrow key. The cursor will jump to the bottom row of data. Then move the cursor to column E and put some text there (it doesn't matter what), then hold control and press the up arrow key. Use control C to copy the formula. Finally, hold shift and control and press the down arrow to select all cells in the column and paste with control V. This will get you the day of the week for all sightings. In column F do the same thing to get the year.

=YEAR(A2)

You know your boss will want a drop-down list to select different shapes, so you'll need a list of shapes without any duplicates. In cell K1 write "Shapes" and copy the shapes from column D to column K. Select the column and go to Data > Remove Duplicates (or press and release Alt, A, M). Select the remaining shapes and go to Data > Sort Alphabetically (or press and release Alt, A, S, A). Finally, add an option for "All" at the top of the shape list.

Create a new worksheet called "Dashboard". Copy the entire list of states from the data and paste them into the new dashboard tab starting at cell B30. Go through the same process to remove duplicates and sort them in alphabetical order. In cells F30:F36 write the days of the week from Sunday to Saturday. Next, copy the years from column F of the data worksheet, and in cell K30 of the dashboard worksheet hold control and alt, then press V, or right-click and select paste special. In the paste special menu select "values." Remember that we used a formula to isolate the year from the date column. If you were to just paste normally, you would be pasting the formula rather than the year value.

In cell C2 write "Select Shape:" and move the cursor to D2. Click the Data tab and click Data Validation. Select "List" in the menu that appears and click the Source field, then select all the shape options from the data worksheet, K2:K23 and click OK. Cell D2 should now have a drop-down menu with the different shape options. Pick "Changing" for now.

Next, in cell D30, write a COUNTIFS formula that counts the number of sightings in the data worksheet where state is "AK" and shape matches the shape selected in D2. Try it on your own, or click the button below to see the formula (remember to lock the row when referencing the drop down list!).

Show Answer

Drag the formula down so you have a value for each state. Next, in cell G30, write a COUNTIFS formula that counts the number of sightings where the day of the week matches the day in F30 and the shape matches the shape in D2. Copy that formula for each day of the week. Then in L30, write a COUNTIFS function that counts the number of sightings where the year matches the year in K30 and the shape matches the shape in D2. Copy the formula for each year. If you need help with these, see the box below.

Show Answer

Formatting the Dashboard

Hold the control key and select columns A, B, H, I, J, and P. Press and release Alt, H, O, W, or click the Home tab > Format > Column Width. Set these columns to a width of 2. Select columns D, E, F, G, K, L, M, N, and O. Set these columns to a width of 9. Change the width of column C to fit the text. In cell B1 write the title of the dashboard. Make the title and the drop down menu a light green color. Now hold control and select B4:B15, B17:B25, and J4:J15. Click the Home tab > Format > Format Cells. Click Fill Effects, check Vertical under Shading Styles, and for color 1 choose black and for color 2 choose a dark green. Repeat the same steps for H4:H15, P4:P15, and P15:P25, but make color 1 the dark green and color 2 black. Fill in the cells between the colored bars (C4:G15, K4:O15, C17:O25) to create three green rectangles.

Adding the Graphs

Select the state data (C30:D79), then click the Insert tab and pick map. Note that versions of Excel older than 2019 don't have the map option, so if necessary you can pick a bar chart or a different kind of chart. Drag the map or chart to the top left green rectangle that you just made. Select the day of the week data (F30:G36) and click the Insert tab and choose a bar chart. Position this chart in the top right green rectangle. Finally, select the year data (K30:L110), click the Insert tab again, and pick a scatter plot. Position this graph in the long bottom rectangle.

Selecting All in the Drop Down

The graphs work fine when we choose a specific shape in the drop down, but what happens when we select All? The COUNTIFS all return 0 because they are searching for sightings where the value in the Shape column match the shape selected in cell D2. Because the word "All" never appears in the shape column, there are 0 sightings which satisfy the rules. You need to change the formula to only match on shape name if "All" is not selected. Update your formula in D30 to include an IF statement, where if D2 is "All" then accept any value in the shape column, otherwise use the formula you've already written. Remember that the wildcard * can match with any text. If you get stuck, the answer is in the box below.

Show Answer

Formatting the Graphs

Go ahead and delete each chart's title, as they take up space and it's already pretty clear what they're showing. You can click each graph and in the Chart Design tab you can select Color and change the graph's color scheme. Next, click the background of each graph, right click, and select Format Chart Area. Under Fill click No Fill, and under Border click No Line. Click the axis labels in each graph, click Text Options under Format Chart Area, and change the text to a lighter color that stands out against the green background.

Changing the Dashboard Background Color

Now hold control and press A to select the entire worksheet. Hold control and use the mouse to select the green rectangles: cells B4:H15, J4:P15, and B17:P25. You have just selected each cell in the worksheet and then de-selected the cells that make up the green rectangles. Now turn the background color to black.

And there you have it. Your boss now has a working Excel dashboard that shows her where and when to find different shapes of UFOs. You can experiment by graphing different types of data as well. You can also use the source data to graph sightings by time of day, or by month. If you have the map widget, you can also experiment by graphing different cities within a particular state. The great thing about Excel is that you can use the data and represent it in your dashboard however you want!