Some people use Excel because they LIKE numbers and math. Some use Excel because they DON’T! Nick counts on Excel to do the mathematical heavy lifting. He needs to calculate rows of data, but the thought of creating formulas and then checking the answers seems daunting. If you feel the same way, you might like the automatic calculations that Excel can do without you having to create one formula!

Imagine simply clicking on cells with numbers and the sum instantly appears! It is faster than looking for your calculator! Once it is turned on, it is always available to view on the status bar every time you select multiple cells that contain data. This is great for a quick peek to see how different salespeople, offices or products are doing.

So, how do you turn it on? Right-click on the status bar (that’s the little toolbar below the worksheet tabs that says Ready). Then, click to check the calculations you want to appear. It stays on until you turn it off in the same location.

This will simplify your year-to-date totals! Let’s say you keep track of the number of views on your website’s pages. You normally create a formula to add the new month’s totals to last month’s totals to create the year-to-date. You have to create a new formula for each new month throughout the year.

Instead of creating all those formulas, simply copy your new monthly total, then paste it over your year-to-date data. EXCEPT instead of using Ctrl + V to paste, use Ctrl + Alt + V. That opens Paste Special. Under the Operation section, select Add, then OK and the totals appear! Then each month, copy your current numbers and use Paste Special, Add to the year-to-date. No formulas, and no problems!

Consolidate is more complex, but it can save you a boatload of time when you need to gather numbers from several different sources on multiple worksheets and then total them all in one sheet. For example, each of your worksheets contain the sales figures for each of your company’s regions. Each line item is a different product which not all the regions sell. What a cumbersome process you would have to follow! First, to sort all the products on each sheet and then to create a formula to collect all the totals ensuring the data matches the products.

Consolidate is a HUGE easy button! It will look for all the same products (even if they are NOT in sort order) and sum them on a new sheet (even if some of the worksheets don’t list the product).

What’s the catch? There is a tiny bit of setup involved: make sure that all the worksheets have the same columns appearing in the same order with the same column names. (Tip: if you receive the worksheets regularly and they are not set up the same, use a macro to restructure them.)

Then, create a new worksheet for the totals and put your cursor in cell A1. Under the Data tab, click Consolidate. In the Reference section, click the collapse button (it looks like a tiny worksheet). Go to the first worksheet and select the data. Back in the Consolidate dialog box, click Add. Check the Use Labels in Top Row and Left Column options. Repeat for each of the worksheets. Then click OK. Once you have selected where the data is, Excel does all the math for you. What a time saver!

It is smart to let Excel do your routine calculations so you can spend your time and brain power on more important things like finding that calculator!

If you have any experience in Excel, then you have probably found a few reasons to want to secure your workbooks. Here’s an example of what happened to Mary. She spent weeks making the perfect worksheet for her sales team to collect data about their sales. Then, accidentally, one of the sales reps deleted five critical columns along with all the information the team had already captured. Yikes! Imagine how you would feel if that happened.

Security is certainly important for protecting high-dollar sales information from those who might unknowingly employ the delete key. Almost everyone who shares a workbook could benefit from some of Excel’s security options. Many of them are hidden gems that most people don’t know about.

Let’s look at a few of them and explore why you might use them.

Windows Properties

If you have a team who needs to see a workbook, but you don’t want them to make any changes to it, a simple solution is in Windows File Explorer. You can right-click on the workbook file name to open the Properties window. You can set the attributes to either Read Only or Hidden.

READ ONLY – Read Only allows them to look but not touch! It’s like when you have the workbook open on a shared drive, and no one else can make changes.

HIDDEN – The other option, Hidden, is very tricky! It actually hides it in plain sight. While it still resides in the same location, no one – not even YOU – can see it. So how do you access it? Save a link to the workbook BEFORE you hide it! Be careful though, if someone pinned the workbook in Excel, they will still have access to it after it’s hidden.

Save As Password Options

This is so hidden, most people never find it on their own. Excel allows you to add a password to the workbook with multiple options: Password to Open, Password to Modify and Read-only Recommended.

Password to Open means no one – not even YOU – can open the workbook without the password! It’s good to keep track of it somewhere – especially if you have a lot of passwords.

Password to Modify is great for a template that you have created for others to use. The only way they can change it is to save it with a new name.

Read-only Recommended reminds you to make it read only when you save it. So where is this hidden gem? When you save a workbook, you will see a link called Options. Click it and the classic Save As opens with the word Tools next to the Save button. Click the Tools dropdown to see General Options and that’s where you can set the password.

BONUS: By the way, in Save As you can also save it as a PDF which means no one can modify the workbook in Excel, but then you probably already knew that one!

Hide/Show

Excel provides a variety of options to hide or show specific information. For example, you can hide or show columns or rows by right-clicking on them. Unfortunately, it’s pretty obvious they are hidden when the header numbers or letters are out of sequence. But this is useful when you want information to just be out of the way while you work on the neighboring cells.

Here’s the gem: Did you know you can also hide a worksheet!? This is beneficial when you have data that will roll up into one worksheet that the boss needs, but you don’t want to confuse him with all the other worksheets that contain the raw data. Right-click on each of the tabs to hide them! To show them again, right click on the NON-HIDDEN tab, select Unhide, then choose which sheets to show from the list.

These are just a few of Excel’s hidden security gems. Start using the ones that make sense for you to help protect your data.

You have a set of offices and sales regions, products and specific business time units (quarters, semi-annual) that you enter repeatedly. AutoFill would be helpful, but the default AutoFill lists do not contain these terms. What can you do? Create a customized AutoFill list. When you have a series of information that always appears or repeats in the same order, you can tell Excel to save your list and recognize it as an AutoFill option.

Create your own AutoFill Series

Select the cells which contain the data you want to comprise your custom list.

Click the File tab.

Click the ExcelOptions button to open the Excel Options dialog box.

Click the Advanced button [A] and scroll to the bottom of the Advanced Options window.

Click the Edit Custom Lists button [B] to open the CustomLists dialog box.

The cells you selected will appear in the Import list from cells: text box [C]. Click on the Import button [D] to import your selection as a list. A new list will appear in the Custom lists: window [E], and the items in your selected cells will appear in the List entries: window [F].

Click the OK button to close the Custom Lists dialog box, then click OK to close the Excel Options dialog box and save your new list.

To use your custom list, place your cursor in a cell, type the first item in the list, and then click and drag the Fill Handle.

]]>http://www.pryor.com/blog/create-a-custom-autofill-series-in-excel/feed/0Spotlight Excel Formula: Convert Text to Numbershttp://www.pryor.com/blog/spotlight-excel-formula-convert-text-to-numbers/
http://www.pryor.com/blog/spotlight-excel-formula-convert-text-to-numbers/#respondThu, 03 Nov 2016 15:00:47 +0000http://www.pryor.com/blog/?p=2768Numbers stored as text can present problems for lookup formulas and regular calculations. There are a few ways to convert Text to Numbers in Excel using a formula. We’ll look at three separate scenarios and see how we can use a formula to make things work the way they should.

Using Value in a VLookUp

In the example below, our formula should have returned Blue, but instead it gave us an #N/A.

Even though the entry in D1 appears right justified, it is a text value (numbers preceded by an apostrophe). The entries in A2 through A7 are actual numbers (values). Rather than manually fix the alphanumeric lookup values, we can simply enclose it in the Value function, which converts a number stored as text to value data.

Combo Cell with Left, Right or Mid

We can apply a similar solution when using Left, Right and Mid to extract data from a combined cell. For example, here we have used these functions to extract numbers in specific positions from the string, 32-ABCD1234V99. We did get the right numbers. But, since Left, Right and Mid are text functions, they return a text value.

If we simply wrap these formulas in the value function, we can get it to return actual values. Notice that the results were automatically right justified.

Simple Math: This multiplied by 1

If you have to convert a whole column of numbers stored as text to values, create a formula that multiples the cell contents by 1. Then, just copy it down. In some cases, you may need to perform a copy/paste values to actually change the value in the cell from a formula. Check out the Multiply by 1 worksheet in the sample workbook.

Finishing Touches

You may still need to format your results in the number format that makes the most sense. These methods may not return the right format. On the positive side, since it is possible that you will have mixed cells, some formatted as text and some formatted as numbers, none of the methods above will “break” a cell already formatted as a value.

]]>http://www.pryor.com/blog/spotlight-excel-formula-convert-text-to-numbers/feed/0Create a 3-D Power Map in Excelhttp://www.pryor.com/blog/create-a-3-d-power-map-in-excel/
http://www.pryor.com/blog/create-a-3-d-power-map-in-excel/#respondThu, 27 Oct 2016 15:41:41 +0000http://www.pryor.com/blog/?p=2761Microsoft’s latest release of Excel has some powerful updates. Our latest 2016 highlight is the popular Power Map add-in. This feature can plot geographic and temporal data on a globe or custom map in a three-dimensional presentation. You can view data changing over time and create animated presentations called “tours”.

Imagine you’re creating a presentation that discusses demographics, say crime rates around the U.S. You want to show regional differences in the statistics, but bar charts don’t clearly communicate where – geographically – the data is from. Bar charts don’t create the impact you were hoping for.

The Steps

Create a 3D Map

Prepare your data:

You must have at least one geographic value in each row of data. This could be City, Country, Region, Zip, State, Address or Latitude/Longitude data.

Format your data into an Excel Table.

Click any cell in the table, then click the 3D Map button [A] in the Tours group on the Insert tab.

Click New Tour[B]. If you are using 3D Maps for the first time, you may have to Enable the Data Analysis add-in to use the feature [C].

A new 3D maps window will open. Excel will attempt to identify the Location field from your worksheet [D]. If it does not, click Add Field and select the correct location column from the dropdown menu.

Adjust the zoom [E] to best view your data. Too close and you will miss some data. Too far and the data can look too “crowded”.

Select the visualization type. If the default bar chart is not the right type for your data, choose from the row of types [F].

If your data contains multiple values associated with each region, add those fields to the Values pane. Click Add Fields [G] and select from the dropdown menu. Excel will add the values to the map and create a legend [H].

Adjust data values by clicking on the arrow beside the field. Choose an option from the dropdown menu [I].

When you have finished working in 3D Maps, click the File tab, then Close to return to your worksheet. A text box may appear to remind you that you have 3D content available.

]]>http://www.pryor.com/blog/create-a-3-d-power-map-in-excel/feed/0Secondary Axis Charts in Excelhttp://www.pryor.com/blog/secondary-axis-charts-in-excel/
http://www.pryor.com/blog/secondary-axis-charts-in-excel/#commentsThu, 20 Oct 2016 14:25:50 +0000http://www.pryor.com/blog/?p=2721If you have two data series that are related, but not comparable, it might be tough to chart it. For example, let’s say you have data representing the sales of products. You also have the production costs. When you subtract the production cost from the sales data, you get the gross profit. Divide this remainder by the production cost, and you get the profit percentage. So, if you’d like to see the profit percentage, which is usually a value of 1 or less, plotted with the sales price, typically a much larger value, using a regular chart style will render the profit percentage virtually invisible. To see both and how they correlate, we’ll need to use a Secondary Axis Chart. A secondary axis chart does not add a 3rd axis or dimension to your chart (Z-axis), it is more like a second Y axis.

Steps

Select your data.

In our case the data we want to chart is not contiguous. So, we’ll select the first two columns containing the Item and sales price, then hold down the Ctrl key to select the column containing the profit percentage.

Insert chart.

Choose the chart type in which you’d like to see your primary data series formatted. We’ll use a column chart. As you can tell, the Profit Percentage data appears to be missing. It’s there, it’s just so small when we compare it to the Sales Data.

Note: Want more room to maneuver? Try using the F11 shortcut to create your chart. By default, this will create it on a worksheet of its own, maximizing the chart size.

Select and format secondary axis data.

It would be hard to right click on this data, since we can’t see it. So, we’ll use an alternate method to select it. This depends upon the version of Excel you are using.

Excel 2010 and Earlier:

On the Layout tab, click the Chart Elements field in the Current Selection group.

In the drop down list, select the “invisible” series, in our case, Profit Percentage.

Below the Chart Elements field, click the Format Selection button.

Click the Secondary Axis radio button.

Excel 2013 and Later

On the Format tab, click the Chart Elements field in the Current Selection group.

In the drop down list, select the “invisible” series, Profit Percentage.

Below the Chart Elements field, click the Format Selection button.

In the Format Data Series task pane, check the Secondary Axis radio button.

Change the chart type of the secondary axis.

Now that you can see it, you can right click on the secondary axis series. Choose Series Change Chart Type. If you’re using Excel 2010 or earlier, choose one of the Line chart selections. In Excel 2013 and later, choose Combo. This will only change the Profit Percentage series, not the rest of the chart.

For what else can I use a Secondary Axis chart?

See the post about Excel Gauge Charts to see how a secondary axis chart can help you create a speedometer like chart with a needle that moves with a changing value!

]]>http://www.pryor.com/blog/secondary-axis-charts-in-excel/feed/1Creating an Excel Gauge Chart with Pies and Doughnutshttp://www.pryor.com/blog/creating-an-excel-gauge-chart-with-pies-and-doughnuts/
http://www.pryor.com/blog/creating-an-excel-gauge-chart-with-pies-and-doughnuts/#respondThu, 06 Oct 2016 16:51:27 +0000http://www.pryor.com/blog/?p=2723You might think you need some fancy add-ins to create charts that look like a speedometer. In this tutorial, you’ll learn how to create this snazzy image from a pie chart and a doughnut chart.

The Steps

Create the data for the speedometer.

If a circle is 360 degrees, then a half-circle is 180. The number and value of intervals depends upon how detailed you want to be. In the sample file, we’ve set up 6 intervals which add up to 180. We’ll need all 7 cells, including the total.

Create the data for the needle.

We’ll also need 3 cells that contain:

Position of the needle, your actual data value

Width of the needle, which is always 1

A formula which subtracts the sum of these values from 360

Create the speedometer with a doughnut chart.

Next, we’ll create our first chart. Select the 7 cells for your Speedometer and insert a Doughnut Chart. You’ll find this in Excel 2010 on the drop down button named Other Charts. In later version, you’ll find it in the drop down button for Pie Charts.

Note: While you can use the F11 shortcut to create a chart. For this activity, it’s best to create the chart in the same worksheet as the data to make all the necessary modifications easier.

Modify the chart to look like the half-circle dial.

Right click the chart and choose Format Data Series. We’ll make two changes in the next dialog box. First, change the Angle for first slice by typing in the number 270 and pressing Enter. Next, change the doughnut Hole Size to 50%. Finally right click the wedge on the bottom and choose Format Data Series. Change the Fill to none.

Add needle data as a pie chart.

Right click the gauge chart you just created and choose Select Data. Click the Add button and Name the new series “Needle.” Navigate to the Series values field and select the Position, Needle and End data. Click OK out of this dialog box. The next steps vary depending upon whether you are using Excel 2010 or earlier and Excel 2013 or later.

Excel 2010 and Earlier:

Right click the outer doughnut and choose Change Series Chart Type.

Choose Pie.

Right click the pie or, in the Layout tab in Excel 2010, in the first field in the ribbon, choose Series “Needle”, then Format Selection, below it.

Change the Angle of the first slice to 270. Then, click the radio button for Secondary Axis.

Excel 2013 and Later

Right click the pie chart and choose Change Series Chart Type.

Choose Combo Chart.

Change the Chart Type for the Needle series to Pie.

Check the Secondary Axis check box.

Note: If right clicking proves tricky, click in the Chart Elements field on the Layout tab (2010) or Format tab in later versions. Then, use the Shift+F10 shortcut to show the “right click” menu.

Modify the pie to look like a needle.

Whether it’s obvious or not, there are 3 slices: two large ones and 1 tiny one that looks like a chart and choose Format Data Series to remove the Fill and Border. (Tip Shift F10, Right Click button). If you want to change the color of the needle, it might be best to zoom in to make sure you’re just selecting the 1 point wedge.

Finishing Touches

As you change the value in Position, you’ll see the needle move. However, it would be helpful to see some numbers here to complete the picture. Next to the values we used for the doughnut chart, cumulative values have been entered from 0 through 180. We’ll use the non-zero values to add data labels which will be the meter positions against which we’ll view the needle. Using the Layout tab method, select Series1 (In 2013, choose the Format tab). Use the Shift+F10 shortcut or use the “right click key” on your keyboard. Choose Add Data Labels. Click the bottom (180) label and delete it. For each of the remaining labels, click the label. Then, in the Formula bar, type and “=” and the click on the appropriate meter label value. Drag them to the right places on the gauge.

The legend really doesn’t inform anything here. So, we can delete it. Let’s add or modify the Chart Title. The sample data already has chart titles. Select the words Chart Title and click into the Formula bar. Type an equal sign “=” and then click on the cell with the appropriate chart title.

Note: Advanced users — Try using Data Validation and a VLookUP to select the values for the “Needle” Position value and Chart Title data.

]]>http://www.pryor.com/blog/creating-an-excel-gauge-chart-with-pies-and-doughnuts/feed/0Calculating Compound Interest in Excelhttp://www.pryor.com/blog/calculating-compound-interest-in-excel/
http://www.pryor.com/blog/calculating-compound-interest-in-excel/#respondThu, 29 Sep 2016 15:05:35 +0000http://www.pryor.com/blog/?p=2711Whether for personal or business application, it’s a good idea to know how to calculate compound interest for loans and investments. There are two methods you can use, the long form and the FV (future value) function.

Long Form

In this first example, we’ll assume a one and a half percent (1.5%) interest rate over a period of 12 months and a beginning balance of $5,000. If we were doing this with a calculator, we’d take $5,000 and multiply it by the 1.5% rate after dividing it by 12 to account for the monthly interest rate. We’d take the result of that and add it back to the $5,000. So:

$5,000 X (.015/12) = .21

To get to the balance with the interest, we simply add the starting balance back to it.

$5,000 + ($5,000X (.015/12)) = $5,000.21

Looking at the sample worksheet tab, named LF-Compounded Monthly, we see that our opening deposit is on 12/31/2015. With interest compounding monthly, each successive row with its end of month date in each succeeding month needs this formula copied down from row the second date row to the bottom to arrive at the compounded interest.

If interest were being calculated on the exact number of days each month and then added to the balance at the end of the month (similar to credit card interest calculations on regular purchase), then we’d need to divide the rate by 365 and then multiply it by the number of days in that period. That would look more like this.

Examine LF-Compound Daily 360 and LF-Compound 365 for true daily compounding interest calculations. Rather than accumulating daily interest and adding it to the balance at the end of the month, each day interest is calculated and added to the balance on which the next day’s interest is calculated upon.

Function Method

The second way to calculate compound interest is to use the FV function. This function requires:

Interest Rate (don’t forget to divide by 12 if it’s an annual rate!)

Number of periods (in our example 12 for 12 months)

Additional monthly payments (0 in our case)

Present value (initial deposit expressed as a negative)

In the sample workbook the example is on the FV Function worksheet.

Which Form to Use?

What we actually created in the Long Form example is an amortization schedule.

This would allow us to see the balance as it increases over time, rather than only at the very end, as with the FV example. If you don’t need all the detail, FV is just fine.

If you’re calculating mortgage, you don’t have to set up a thing! Just look for the Loan Amortization Schedule template available for any version of Excel. Just search in templates or choose Sample Templates in Excel 2010 to find it.

]]>http://www.pryor.com/blog/calculating-compound-interest-in-excel/feed/0Accounting Magic – What You Need to Know About Accounting Excel Formulashttp://www.pryor.com/blog/accounting-magic-what-you-need-to-know-about-accounting-excel-formulas/
http://www.pryor.com/blog/accounting-magic-what-you-need-to-know-about-accounting-excel-formulas/#respondThu, 22 Sep 2016 14:22:07 +0000http://www.pryor.com/blog/?p=2690Accounting is more than just debits and credits. It is the calculation and analysis of those debits and credits that generate accounting information an organization can act upon. Let’s take a look at two types of reports a professional in the Accounting Department might be asked to generate. Then, we’ll see how that information can be used to produce ratios which can be used to determine how a company is doing compared to others of its kind, if their data is known.

Balance Sheet

Depending upon the industry and make-up of the organization, the balance sheet might contain different line items. However, by and large, the top part of the balance sheet contains the assets of the organization, followed by the liabilities. The difference between these is known as Owner’s or Stockholder’s Equity. There can be several subsections, for example, Fixed Assets, Current Assets, Goodwill and Intangibles. We would add up all the items classified as current assets, then all the items classified as fixed assets to arrive at total assets.

We do the same for our current liabilities and long-term liabilities. Current liabilities would be things like payroll and income taxes. Long-term liabilities would include long-term debt. We would add up all the items under current liabilities, then long term liabilities, then the total of those.

If you report several years together, you can utilize Spark Lines to show growth or decline over the reporting periods. You can also create additional columns to calculate the percentage growth or decline for more impact. If you are generating a report template, you may have no figures entered yet. In that case, your percentage formulas may show #DIV/0 errors. Of course, you know they’re not really errors, there’s just no data yet. You can use the IFERROR function in front of the percentage calculation to show a blank or zero until accurate numbers are entered. In the sample workbook for this article on the Balance Sheet worksheet, we’ve applied all these techniques to Assets.

Income and Expense Statement

Another useful report is the Income Statement, also called the Income and Expense Statement, and Revenue Statement. Unlike the Balance Sheet, which is a snapshot in time about how we’re doing. An Income statement reports a specific period of time to show how we earned (or lost) what we earned (or lost). Simply stated: How much did it cost us to earn the revenue we made? Like the balance sheet, the composition of the income statement can vary depending upon industry or complexity of the operation. In our example, we’re using easy line items, such as:

Revenue

Cost of Goods Sold (COGS)

Gross Profit

Selling and General Administrative Expense

Depreciation and Amortization

Operating Income

Interest Expense

Other Expenses

Income Before Taxes

Income Taxes

Net Income

Income statements may also include additional shareholder impact information. The calculations performed are:

As you can see in a worksheet with these calculations, at first glance, it may not be easy to tell where the formulas are. Use the Show Formulas tool (Ctrl+`). To see where all the formulas are before beginning to edit to make sure you don’t delete any formulas.

Ratios

All of the items above when used in various combination and calculations yield rations that allow us to compare ourselves to others in our industry and determine how we’re doing in comparison. It is important to research and determine what is “normal” or “good” for our particular industry. It wouldn’t do any good to compare the ratios of the corner meat market with that of IBM!

By using named ranges to name each relevant line on the Balance Sheet and Income statement, we can just type in these terms and the relevant math operators to get the ratios. If you highlight both the name of the entry and the entry, use the Create from Selection button on the Formula tab in the Defined Names group to create the named ranges.

]]>http://www.pryor.com/blog/accounting-magic-what-you-need-to-know-about-accounting-excel-formulas/feed/0How to Work with Excel PivotTable Dateshttp://www.pryor.com/blog/how-to-work-with-excel-pivottable-dates/
http://www.pryor.com/blog/how-to-work-with-excel-pivottable-dates/#respondThu, 15 Sep 2016 14:14:57 +0000http://www.pryor.com/blog/?p=2674With the most recent versions of Excel, you have gained more and more functionality with Pivot Tables. It is now much easier to work with dates. There are two features that you can put to work right away: data filtering (2007) and timeline slicer (2013). There’s also a change to the way dates are displayed by default in an Excel 2016 PivotTable. We’ll show you how to use it, how to turn it off and how to get similar functionality in earlier versions.

Date Filtering (Excel 2007+)

With the introduction of Excel 2007, you were able to filter date data in a column by date range, for example, this quarter, last quarter, this week, last month, next month, between two dates. This made life a lot easier for those whose data must be analyzed in this way. Any date field you add to a row or column field (not value field) in a Pivot Table can take advantage of this feature. Just click the down arrow at the top of the column with the date. If you have several row fields, be sure you have a cell selected that shows date data. That will enable the date filters.

Unfortunately, this same functionality is not available if you add a date field to the Report Filter area. If you are using Excel 2013/2016, you can take advantage of the new Timeline Slicer.

Timeline Slicer

Slicers were introduced in Excel 2010 and could, more or less, take the place of the Report Filter (Filters) function in the layout. However, it did not give us any additional facility with dates. In Excel 2013, we can take advantage of the Timeline Slicer. To add a slicer:

Select a cell inside your Pivot Table.

On the Analyze tab, in the Filter group, click on the Insert Timeline button.

If you have more than one date field in your source data, choose the date field you want to use as your slider. In our example, there is only one, Date. Check the box, then OK.

The default measure will likely be Months. Click the down arrow to the right of the word Months and choose another measure, if you like.

Date Grouping in Excel 2016

Somewhat alarming, if you weren’t expecting it is the way dates now appear as a row field in a Pivot Table in the newest version of Excel. By default you’ll noticed dates are grouped by year, quarter and month, with + or – symbols to expand and collapse within these groups. Filter still work, but if you’d prefer to see just regular dates, right click on any date grouping and choose Ungroup.

How to get Month Names in 2007-2013

No matter what version you’re using, to get a month name, rather than a date to show up as a selectable field in an Excel Pivot Table, add a column to your Source Data with this formula, where A2, refers to the dated cell. =TEXT(A2,”mmm”).