All Spreadsheet Topic Suggestions

You are here: Home : Spreadsheet Tips : All Spreadsheet Topic Suggestions

1

Why would the GetPivotData command work for all columns but the last one? I have a spreadsheet in which I use VBA code to create a GetPivotData formula in a row that is outside the range of the Pivot Table. I loop through the Columns ("Day of Week") adding only certain rows when they match a list of initials (JF, NE, RH) . The VBA formulas work correctly for Monday through Friday but when it creates the formula for Saturday it returns the actual date (4/30/2012) instead of the work "Saturday." Thus the formula give a #REF error. Why does it work for all other days except Saturday? Correct formula looks like:
=GETPIVOTDATA("Day of Week",$A$4,"Packer Initials","JF","Day of Week","Monday")+ GETPIVOTDATA("Day of Week",$A$4,"Packer Initials","NE","Day of Week","Monday")

Incorrect one which returns 4/30/2012 instead of "Saturday":
=GETPIVOTDATA("Day of Week",$A$4,"Packer Initials","JF","Day of Week","4/30/2012")+ GETPIVOTDATA("Day of Week",$A$4,"Packer Initials","NE","Day of Week","4/30/2012")

Posted at 2:34 pm ET on June 11, 2012
by John Collier
2

Concatenate and place in specified length:

Bill,

Greetings. I have a very tricky issue. We are trying to set up an excel based journal entry upload file into our ERP system. We intend to use our customer & project code as the last 10 digits of the description field on the journal entry. However, those 10 digits have to begin at exactly 28th letter of the description. (That way we can run reports using the description field) The journal entry description therefore has to be a maximum of 26 characters / letters.

The concatenate function is what would work. Lets say, on Column A the description is entered and in Column B the 10 digit customer project code. We would like to concatenate Column A and B and use that for description for the Journal entry. The issue we are running into is that the description on column A could be from 2 to 50 letters. How do I cut off the description to a maximum of 26 and ensure that the customer / project code always begins on the 28th letter of the concatenated description? (even if column A has only 10 letters) How can we accomplish this in excel? Would really appreciate your thoughts and help with this.

Shrikant Sortur

Posted at 12:37 am ET on November 2, 2011
by Shrikant Sortur
3

Just as you can select to repeat rows at the top of a page as you print it, is there a way to repeat rows at the bottom of a page as you print it?

These rows would be in addition to the information you put in your footer.

thanks,

David

Posted at 8:23 am ET on August 22, 2011
by David Horn
4

Using the "Find" feature in Excel is nice to locate items quickly, but is there a way to use this or another function that will expand the search to include text in any comments within the worksheet?

thanks,

David

Posted at 11:10 am ET on August 19, 2011
by David Horn
5

How do you get the data fields in a PivotTable to format the same as the source data? If my source data is formatted as Accounting with two decimal places and $, I want the same field in the PivotTable formated the same way. Why do I have to manually format the field again in the PivotTable when I already did it in the source data?

Posted at 9:20 am ET on August 6, 2011
by John Keegan
6

Sir,
I am developing a compliance tracking model in Excel 2007 with Macros. This model has the list of reports, report due date, responsible person's email and report status (Pending or Submitted).If I open the file at anytime and refresh it and the model should check all the status and send an automatic email (outlook) to responsible person for all the status reading as "Pending" with a copy to the CFO.
What is the best and easy step of modelling this requirement?

Posted at 8:46 am ET on July 19, 2011
by Ebrima Sawaneh
7

In pivot table, how do I "suppress" or eliminate those cells where across all columns+rows in that particular line+colum the summation is zero. I don't want to see that line/column to appear in the pivot table for presentation purpose as it is not revelant to reduce size.

Posted at 12:55 pm ET on June 24, 2011
by Victoria Chan
8

I am looking for a way to reference a row number for data located in one spreadsheet based on a "reference" number in a cell located in a "summary" spreadsheet. In my summary spreadsheet I have several cells that reference data from an another spreadsheet in the same workbook. The data table is updated weekly by date and I need to reference a new row number each week. I would like to insert a row number in a cell in the "Summary" spreadsheet and use that cell to reference the row number in the formula that references the data in the "Data" spreadsheet.

Posted at 12:46 pm ET on June 24, 2011
by Walter Johnson
9

Can a single worksheet have the trust center enabled so it doesn't always block content. But not have it changed globally?

Earlier Excel versions you could allow a worksheet to accept links, etc. Now it appears to be all or nothing with out being asked all the time.

Thanks,

Posted at 4:27 pm ET on June 23, 2011
by Wayne Rowley
10

I use pivot tables for data analysis; great tool but I have a problem when my data range includes blank rows. When I select a field for values, the default is to "count" versus "sum". Easy enough to change but it would be great to not have to repeat this step for each column of the pivot table. Any suggestions???

Posted at 10:38 am ET on June 16, 2011
by Joseph Szczepaniak
11

FORMATTING REPORT BETWEEN OPERATING SYSTEMS:
I often do work on my personal computer which is a MAC using Excel 2008. I will then send the file to someone in my office using Excel on a Windows PC. The problem I am having is how the report formats. The main area is in text footnotes on the report that I originally had wrapping with in a wide cell. The footnote wrapped to 2 lines in the cell, but when it was opened on a PC it needed 3 lines and I did not have the row high enough so it now cutoff the last part of the footnote. How do I avoid this problem while I still work on a MAC in my home office?

Posted at 12:59 pm ET on June 2, 2011
by Jeffrey Matthews
12

Is there a way to change the default Zoom magnification under the View tab from 100% to 75%?

Thanks,
Tom

Posted at 4:40 pm ET on June 1, 2011
by Tom Hasler
13

In an Excel sheet, where you have a column with repeating text values (e.g. state codes), a common problem appears when you want to assign each value a description or values that re-arrange your column in a new look fashion (for some special purposes for example).
This problem is, of course, resolved when you can read the assigned new values from other sources, by using VLOOKUP function for example. But if you are required to enter values manually, the only option I have found is to use IF functions, nesting one into another, to get the issue resolved.
In ACCESS we have SWITCH function available in SQL Query to tackle such a burden; is there a similar solution in hand to be used in EXCEL too?

Many thanks in advance.

Posted at 5:28 am ET on June 1, 2011
by Raveed Khanlari
14

Our accounting software combines columns when it exports to Excel. Is there any shortcut to take this combined data separated by a space in one column and split them into their three separate columns again?

Posted at 11:12 am ET on May 28, 2011
by GRACE BLACKBURN
15

How do I list file names along with path of my all excell files on the PC in a worksheet that automaticly open when I boot my PC ?

Posted at 8:11 am ET on May 26, 2011
by Vijay Kedia
16

This is a follow up question for referencing a cell to create or change a header or footer. How do you format the header or footer using the macro? I've got one long title now, how can I get it to drop into additional lines?

thanks again.

Wayne

Posted at 2:20 pm ET on May 19, 2011
by Wayne Rowley
17

how do i link a cell to the header so I only need to change my cell reference once to change multiple worksheet headers?

Thank you!

Posted at 5:32 pm ET on May 11, 2011
by Wayne Rowley
18

I am utilizing Pivot Tables to analyze some large amounts of data on purchases. Each row of my pivot table is the ID # of a product so I can clearly see what products are purchased the most frequently and for the most dollars. I also have 2 columns - "Max of Unit Price" and "Min of Unit Price." I would like to add a third column "Price Range" that is simply the difference of the two columns already in the Pivot Table. It appears the calculated field option will not work - every row ends up with a value of $0.

I have searched the internet to find a solution. Unfortunately, while I have found others with the same question I have yet to find a resolution. Any help would be greatly appreciated!

Posted at 4:12 pm ET on April 12, 2011
by Kelly Wedge
19

I created VBA Useform for data entry into a table in a worksheet. My intention is for users to be able to fill sales information in the table. However I realise that anytime I input a new data, the new data is always replacing the earlier data. In other words, all the input data always drops in one row. How do I make sure the new input data move to a new row (a new line.

Posted at 5:43 am ET on April 4, 2011
by Ebrima Sawaneh
20

I have created a workbook for multiple users which I various groupings in, but I have also protected each of the sheets, and as a result can not utilize the groupings.

Posted at 7:38 pm ET on March 25, 2011
by delete Smith
21

Using Excel 2007 we would like to present two tables of information on the same worksheet one set of data in rows above the other. The catch is trying to have different column widths, or merge selections between the top table and the bottom table.

Posted at 12:32 pm ET on March 18, 2011
by Derek Krause
22

I am often trying to compare similar information in different spreadsheets using vLookup, however, I consistently run accross instances when I would like to have a multi-condition vLookup. I have read that Excel has a formula writer. How do you use the formula writer to create a multi-conditional vLookup (similar to a sumifs)? Thanks.

Posted at 2:30 pm ET on March 16, 2011
by Greg Montgomery
23

When prepping productivity analyses, I work with two databases that display employee names differently - one shows First Name Last Name (ie. John Smith) and the other shows Last Name, First Name, credential (ie. Smith, John RPT). It would be helpful if I could get one database to recognize the Last Name in the other database. Is there a faster way to do this than using text-to-columns on the name fields for both databases? Thank you!

Posted at 4:00 pm ET on March 11, 2011
by Sarah Long
24

I think it's pretty simple, but I am just not sure what the "trick" is to make it do what I need.

I have a column which has each day of the month in it (e.g. 1, 2, 3, 4, etc...).

All I want Excel to do is when I open this spreadsheet go to the cell that has today's day of the month in it.

For example, if today is Oct 1, 2010, I want Excel to go to the cell that has "1" in it for the 1st of the month.

Does that make sense?

This spreadsheet lists what is due on each day of the month by day (e.g. 1st).

thanks,


David

Posted at 3:03 pm ET on March 1, 2011
by David Horn
25

Every month I download an excel file from our financial reporting system that contains the P & Ls of various cost centers (over 100 tabs, one for each of the cost center). I have to generate an excel report summarizing the Revenue, Operating Income and EBITDA for each of the cost center. Currently, I manually select data and do a copy paste to a new sheet. Is there an easier way to summarize data from various tabs into a new sheet? (Each of the tabs are uniform in terms fo the columns and rows that it contains) [Our financial reporting engine cannot do this for us, and that is why we export to excel and do this manually].

Posted at 4:16 pm ET on February 21, 2011
by SHRIKANT SORTUR
26

I am trying to format a cell based on data from another cell in the same workbook. I've tried to use conditional formatting but I can't get it to work properly. Here is an example, Cell A2 is $1100.00 and cell C2 is blank. When cell C2 is populated with a Y (means yes, money has been swept)I would like cell A2's font to change color. Is this possible? Thank you, Ed

Posted at 9:47 am ET on February 18, 2011
by Edward Kennedy
27

When I add a field to the values section of a pivot table, Excel will append "Sum of" of "Count of" to the beginning of the field name and format the numbers using a general layout. As a result, I have to go in and manually strip out the characters "Sum of" or "Count of" and reformat every field added to the value section. Is there some way to turn off the "Sum of" and to establish a standard formatting for all numbers added to the values field?

Posted at 6:24 am ET on February 14, 2011
by Linda Nimmons
28

I would like to find a way to embed the rank number next to the data in a pivot table without having to have the data sorted. I have multiple pieces of data (in separate columns) associated with a company, each having a potentially different rank for that company. I want the rank numbers to change for each associated column as the pivot table changes by including/excluding/adding rows (companies). The company data is sorted in alphabetical order.

Posted at 11:55 am ET on February 12, 2011
by Gothard Friesen
29

In using the NPV( ) function, the calculations always assume that beginning of the series is in the uppermost cell in a column or the left most cell in a row.

For example suppose I had the years 2011 to 2020 in Cells B 1 to K 1 and the income corresponding to each year in cells B2 to K2. If I use the NPV function [i.e. =NPV(7%,b2:k2)]to compute the present value it will provided the correct answer.

Suppose that the data is set up such that years 2020 to 2011 are in cells B1 to K1 and the corresponding income values are in B2 through K2. Because of the order in which the cells are set up I want the present value to go from K2 to B2. I again use the NPV function but I set it up as follows:
=NPV(7%,K2:B2). Note how I reversed the order in the NPV function because the order was changed in the spreadsheet. If I use this second function Excel will still do the calculation using B2 as the first year and will yield the same answer as it did in the first calculation.

The same is true if the series were set up in a vertical column.

How can I change the direction of the computation?

Thanks

Posted at 12:34 pm ET on February 11, 2011
by Jerrold Katz
30

Tips on how to heat map a spreadsheet so that values can be highlighted with different colors from best to worst.

Posted at 8:36 am ET on February 11, 2011
by Alan Kushner
31

Currently, we import MTD sales data into Access, then copy and paste into Excel 2003. I'd like to skip Access and import directly into Excel. I tried using Data > Import External Data > New Database Query > and it sort of works. After selecting the database and entering my password, I select Transaction_Date, Store_No, and Net_Sales. Then, I choose dates greater than or equal to 2/1/2011. Then, the spreadsheet has a row of sales data for each store-day. Is there a way to group all the dates together so that the import shows only sales data for each store? I can do this grouping in Access but I don't know how to do this in Excel.

Posted at 12:37 pm ET on February 10, 2011
by Brad Kanter
32

I have a standard work sheet used to calculate draft invoice values for assignments done by the firm. the sheet contains a row for each client and once I enter the basic fee and disbursements the sheet has formulas that calculate taxes and gross invoice value. I then have create a draft invoice where i copuy and paste each of these values into a vertical format with the cleint name on top. i.e vertically with typically an excel sheet for each client. Is there any way in which i can get this done quicker without having to do it one client at a time.

Posted at 8:55 am ET on February 5, 2011
by Ahamed Fawas
33

I have a spreadsheet with rows of data to be imported. Certain records fail validation testing and generate an error log that can be exported to an excel file. I would like to use conditional formatting in the original data to highlight those records that have generated errors so that they can be easily found and corrected. For example, a period balance for a general ledger account does not import into the budget software because the gl account number doesn't exist in the budget database. I want to highlight this record in the original import file by matching it against the gl account number in the error log. Is there a formula that can be used in conditional formatting to accomplish this?

Posted at 4:25 pm ET on February 4, 2011
by John Patton
34

Seems to me, back in Excel 2000 (maybe 2003), there was an icon to email a single spreadsheet out of a workbook.

I am using Excel 2007 now and can't find it anywhere.

Besides copying the sheet to a new file, is there another way?

Thank you

Posted at 10:41 am ET on January 28, 2011
by Harley Sherman
35

I have two similar databases of customer data in separate spreadsheets. Each record consists of a row with the same fields in each worksheet. The first worksheet is an older file, while the second file is newer and contains the records in the first file, plus hundreds more. I have identified records in the first worksheet, which must be deleted in the second worksheet. What's the quickest and easiest way to do this without going through one by one to delete the records in the second sheet? (I don't want to delete the records in the first worksheet).

Posted at 10:05 am ET on January 25, 2011
by Jeff Moskovitz
36

I have a worksheet where I track year to date sales invoice detail. I add new data to the spreadsheet each month. I then run various pivot tables against the data which include filters. The first time I created the pivot table, the drop down filter selections sorted in ascending order. As I have added records to the worksheet, the data from the new records do not sort in ascending order in the drop down filter. Instead, they are added to the bottom of the filter list. Is there a way to have all data in the drop down sorted as I add information to the worksheet? Thanks.

Posted at 10:32 pm ET on January 15, 2011
by MIKE ELLIOTT
37

Is there a way to have a high and a low number disregarded (or thrown out) when averaging a set of numbers using a formula?

Posted at 10:20 am ET on January 14, 2011
by Angus Tucker
38

Is there a way to have a field in a pivot table without having a function performed on that field? For example, if my data for my pivot table already contains the fields for Revenue Gross Profit$ and Gross Profit %, how can I include the Gross Profit % field without having the pivot table sum up, count, etc. the existing GP% included in my data? Do I have to create a formula within the pivot table itself or is there some other way?

Posted at 4:35 pm ET on January 13, 2011
by Shirley Wiliani
39

Bill,

To better explain, (Conditional Format a Chart topic), I had already selected solid fill yet only 1 color selection becomes available (not two as in your tip answer).

I tried deselecting and selecting again, yet still only 1 color option becomes available.

Is there a setting I need to change (using Excel 2007)?

John

Posted at 10:39 am ET on January 6, 2011
by John Phipps
40

Bill,

Thank you for you excellent help on "Conditional Format a Chart" that I asked assistance on earlier. However when I follow the steps and arrive at the Format Data Series dialog box the "Vary colors by point" DOES NOT appear. I can not select anything. I am using Excel 2007 and went to the help, asked the same and same response to select "Vary colors...." box, yet not there??

Is there a setting some place I have to change, initiate, click, etc.???

Sincerely,

John

Posted at 10:30 am ET on January 6, 2011
by John Phipps
41

I've got multiple worksheets that I have headers where each month I need to change the date. Is there a way to make this easier to automatically look to one cell and update the date on all headers accordingly?

Thank you,

Wayne M. Rowley
Excel User

Posted at 11:49 am ET on December 29, 2010
by Wayne Rowley
42

I frequently develop Excel templates with a number of sheets. However I protect the sheets, one by one. I want to find out if there is any fast method to protect multiple sheets at the same time.
Ebrima, The Gambia

Posted at 6:08 am ET on December 21, 2010
by Ebrima Sawaneh
43

Whenever I do pivot tables I have a preferred way of showing the data. For example on the value field settings I almost always want to see "sum" rather than "count of total". Likewise, the defaults on the pivot options are not my preferred options. Is there a way I can change these defaults so that my preferences automatically are used rather than having to change the settings each time.

Posted at 9:40 am ET on December 16, 2010
by MARILYN KAMMILLER
44

Is there any way to Conditional Format a Chart Range? For example I have a series of numbers in a Balance Sheet Model (Cash Line) that are currently negative, thus when I chart I make the bars red. If I put in Additional Paid in Capital certain months on the Cash line may turn positive and thus I would want those bars representing the positive cash months to be green -

Posted at 2:34 pm ET on December 14, 2010
by John Phipps
45

I have a spreadsheet I use when dispersing payroll for employees to initial when they received their check. When I get new employees or someone terminates I have to redo the columns so they remain alphabetical (I have 2 columns of names and spaces for them to initial). Is there a way to add or remove just 1 or 2 cells in each of the 2 rows (name and space to initial) without it removing or adding in both employee name lines?

Posted at 12:36 pm ET on December 7, 2010
by Charlotte Hammond
46

Should Excel be considered as a tool for reporting from major ERP systems like SAP & Oracle. What other components are needed to make Excel secure, auditable and efficient if used like this?

Posted at 11:57 pm ET on November 23, 2010
by Stephen Evans
47

What's the most efficient method for consolidating data from multiple tabs of a worksheet that all share the identical format?

Thank you for your consideration.

Posted at 4:22 pm ET on November 22, 2010
by Robert Aguirre
48

If you have a list of employee names in one column showing last name, first name (Smith, John), how do you convert the list to show first name last name (John Smith)?

Posted at 3:06 pm ET on November 19, 2010
by Cindi Miceli
49

Is there a way to use conditional formatting to format cells which cells are inputs and which cells are formulas? Also, is there a way to use conditional formatting to highlight where a cell contains a combination of formulas and inputs?
e.g., =sum(a1:a30)+1

Posted at 3:48 pm ET on November 14, 2010
by Nancy Piwonka
50

In the good old days, I used to be able to send financial statement reports from my proprietary accounting software to excel and then combine them into one workbook with multiple worksheets. In Excel 2010, I now get an error message regarding the size of the host spreadsheet being smaller. Is there a way around this? I can move the smaller to the larger but this seems an unnecessary hassle.

Posted at 8:51 am ET on November 8, 2010
by Julia Parr
51

Is there any way in Excel 2007 to have a pivot table always default to sum the amounts rather than count them. Also can the number format in a pivot table be changed from the standard Excel format. Nothing I've tried works.

Many thanks
Eric

Posted at 5:54 pm ET on October 29, 2010
by Eric Sinclair
52

I've tried using the custom feature in formatting cells but I can't seem to get it to work.

How do I get punctuation out of SS #'s?

Submitting csv. files to the state with employee ss#'s they want it with out the punctuation, my accounting software includes it. How can I easily get it removed? I've been adding columns and doing the concantenate formula but that leaves out the zeros when they are in the wrong place.

Any good ideas to help would be appreciated.

Thank you.

Posted at 12:44 pm ET on October 28, 2010
by Wayne Rowley
53

I know how to toggle between workbooks and open windows using the alt-tab and ctrl-tab keys. My question is whether there is a way to reorder your open windows so you don't have to toggle through so many to get to the one you want. Thanks.

Posted at 9:31 am ET on October 26, 2010
by Kelly Thornburg
54

How do you add up advanced filtered rows with out all the stuff in the middle being added to the total as well?

I could really use this option but I always get the whole not just the filtered items in the total.

Thank you,

Posted at 1:40 pm ET on October 20, 2010
by Wayne Rowley
55

How do you display numerical values in thousands while retaining the numerical values? For presentation purposes you may want to display the dollars in thousands (or in millions). Thanks.

Posted at 3:53 pm ET on October 19, 2010
by Rolando Jimenez
56

Suppose, I have data in this format

Target Achievement %Achieved
30538 16423 54%
40245 19015 47%
41110 21080 51%
12039 9263 77%
7530 3454 46%
14507 9585 66%

I want to draw a column chart showing Target vs. Achievement and then want to add line which shows % achievement. How it can be done using Excel 2007 ?

Posted at 5:39 am ET on October 17, 2010
by Deepak Mundra
57

An often overlooked function within Excel is the 'Indirect' formula, which significantly expands the power of VLOOKUP and SUMIF formulas, among other things -- I think a columnn on this would be quite useful.

Posted at 2:25 pm ET on October 15, 2010
by Scott Johnson
58

How can I determine if a cell is being referenced in another worksheet? When I use the Trace Dependents or Trace Precedents buttons on the Auditing toolbar, it shows an arrow pointing toward a worksheet icon. Where I'm stuck is that I can not determine which worksheet or cell is being referenced.

Posted at 2:43 pm ET on October 14, 2010
by Brad Kanter
59

How can you toggle between worksheet tabs that are in the same Excel file?

Posted at 1:26 pm ET on October 8, 2010
by Danny Dale
60

How can I pull data from multiple data spreadsheets into one Pivot Table

Posted at 11:42 am ET on October 7, 2010
by GERILYN BOTTING
61

I use a workbook (created years ago by someone who is long gone) that includes push buttons that navigate to different sheets in the workbook. How do I move these buttons around on a sheet or remove them all together?

Posted at 10:47 am ET on October 7, 2010
by Greg Richmond
62

I need to code or map the current year's Excel trial balance for tax purposes to come out with a sort of tax return Profit and Loss (P&L) / Income Statement.

Last year's trial balance was coded for tax purposes. I plan on using vlookup formulas to match the current year's account numbers to the prior year's account numbers and have the prior year's tax codes posted to the current year's trial balance spreadsheet. After the tax codes are posted I turn the vlookup formulas into values by using copy then paste value. Any new accounts I code manually.

After this I plan to use =sumif and / or =dsum formulas to accumulate tax amounts for the tax "P&L" workpaper. Will the above work and is there a better way of getting the figures?

Posted at 9:23 am ET on September 29, 2010
by Dean Larlee
63

I found a nice link that talks about how to protect a sheet. http://www.techonthenet.com/excel/cells/protect2007.php

It shows how to protect a single sheet by going through and Unlocking, Protecting Formulas, Locking and Adding a p/w to the sheet. If I have 15 sheets and I want to use the same p/w for each tab, how can I select all tabs and do the process once instead of 15 times?

Posted at 1:32 pm ET on September 13, 2010
by Amar Shrivastava
64

Suggestion:

How to use Conditional Formatting in Data Tables (sensitivity analysis) to automatically color the current input and output values in the model, and the max and min output values.

Posted at 1:29 pm ET on September 10, 2010
by Fernando Arellano
65

I would love to see an article about how to export excel to word and powerpoint effectively for Board Presentations and other external communications to eliminate recreating the data in word/powerpoint.

Posted at 10:33 am ET on September 8, 2010
by Ciara Beggan
66

Often I am presented with a download of information in a PDF or Adobe format. This "data dump" needs to be converted into either excel or access so as to be analyzed more easily. Can you provide best practices to accomplish this?

Thanks!

Posted at 9:32 am ET on September 6, 2010
by Ray Schaetzle




Reader Posts