I’ve been driving Excel since it first came out (1986?), and have created enormously complex spreadsheets, VBA functions and subs, and charts out the wazoo. I’ll answer questions until my brain gets tired …
I’m working on the Mac (for which Excel was first written), so a bit of translation may be necessary. I’ve driven Excel a lot on PCs as well, but I don’t have one handy to verify everything. They are almost identical, though, so it shouldn’t be a problem.
I want to turn off Excel’s “cleverness” in cell formatting. For example, suppose a bunch of cells in a column are colored orange. OK, now I’m typing in a blank cell at the bottom, and Excel just knows, is certain, that after I hit Enter I want this cell orange too. Not!
I don’t want to dick around with formats, etc. I just want to globally shut off this poorly conceived AI.
Is there a way to copy and transpose simultaneously? For example, let’s say I wanted cells (A1…A10) to be referenced in cells (B2…K2). How would I do it, besides typing in a formula for each individual cell?
On the Mac, there’s something called “Preferences” under the Excel menu. It’s called something else on the PC, “Settings” or “Configurations” or somesuch. Might be on the “Tools” menu.
In any case, find that sucker and go to the “Edit” pane, and unclick “Extend list formats and formulas”.
Hold down the Command key (propellor or apple key) on the Mac, I believe it’s the Alt key on the PC.
Hit Return
This enters the formula as an “array” formula, which is indicated by braces “{}” appearing around the formula. Note that once you enter an array formula in a range, you can’t change just one of the cells. It will give you an error message saying something like “You cannot change part of an array”. You have to erase the entire array and start over.
How do I use the VALUE command with text?
I have one cell that will say something like “103,4,6” and in the other cell it gives me some sort of error “#VALUE” I think. They are both set to text BTW. All the other cells around it work, but they are just numbers.
Oh, another one that I’ve been wondering for a while. Can I copy cells from various files.
That is, I have an excel file from each day of the year. Could I say get cell B:12 from each file and paste it into a column or row in a new file? Does that make sense?
Every time I create a graph in Excel, I have to adjust the chart and axis titles to the font style I like from the default. I also hate that the plot area is always filled with grey - I want it white. Is there any way to define the defaults to what I like instead of what MS likes? Keep in mind that I use multiple types of charts, line, X-Y, column, pie, etc, so I don’t want the chart type to be part of the default setting.
Thanks!
I have a simple problem.
I build tables in excel and would like to have them linked to display tables in my word documents. In principle this is easy, in practice it caused me all sorts of very strange errors. Several years ago I when I first did this, the word document would print with earlier versions of tables-sometimes with earlier versions of the word document. Tables would only partly update-it was a mess. At first I thought it was just that I created the tables on the mac and sent them to a pc for printing (very embarrassing when the PC is owned by one’s boss who doesn’t like Macs), but the problem would occur even when I built everything on the PC.
Anyway, the question is, does this work now? I still use Word and Excel X for the Mac.
How do I make a histogram? I have dates in column A, several columns of data, and two columns I want to graph along a timeline. First, I can’t get the dates to appear along the X-axis. (I get pseudo dates – they are formatted as dates, but they are not the dates in column A.) Second, the ‘dates’ are equally spaced instead of stretched out as one would expect when there are time intervals between updates.
How can I sort by colour, once I’ve colour-coded a spreadsheet? Also, is it possible to do “if-then” type functions? What I mean is “If column B is not Null, enter today’s date in Column A”
Thanks again. I have a question about your answer in another thread. I figured since my question is somewhat different from the OP in the other thread, I would move over to here. The other thread asked how to automatically create names for cells.
You said:
Excel has a menu item that automatically names cells in the manner you want, it’s Insert:Name:Create … select both the names and the cells, and use the menu item.
I can’t get it to work, I can’t scope out the steps.
I have a cell that I want to name. I have a source for that name. The source could be the first cell in the column containing the cell to be named, or the first cell in the row, or the name of the worksheet (in case that is possible), or the contents of another cell. How do I use the insert:name:create to accomplish the naming of the cell? I try to select the various combinations, but I get a box asking me to check “top column” left row etc, and always get an error.
Building a summary sheet.
I have a workbook that contains about thirty worksheets each providing months information. I have another workbook that selects individual cells from each of the thirty worksheets and provides a summary. So far, each. My problem is that the summary sheet shows historical information-last months info. That info is recorded in the individual sheets, so I simply have to manually go through the summary page and add another line with new references to display the current month. I use names so at least I don’t have to manually select and paste special each cell… But I have to create the names by hand in each individual sheet and type in (or do a global replace) the names in the summary sheet. Each step is slow and error prone.
Is there an easier way?
I want to just fill down in my summary sheet and have all the cells refer to the new locations in the individual sheets. That would be nice-but it doesn’t work of course.
Any help would be appreciated.
More info needed for the first, but you might want to try CTRL-1 while highlighting the cell in question to change it from text to number or general. The #value means there is something not quite right with the formula or calculation or reference or ???, not necessarilly that the value itself is wrong.
You can easily copy cells from other spreadsheets. Go to the cell you want, hit CTRL-C (for Copy), then go to the new file and find the cell where you want it and go Edit > Paste Special > click Past Link. It will enter a formula that will look like “=[Book1]Sheet1!$A$1” or something similar. Although normally automatic, ‘Update Remote References’ needs to be clicked under Tools > Options > Calculations tab. When you open the new file, it might ask to update the remote refs. Look in Help for Paste Link for more info and examples of uses (you can do whole regions of cells from one to the other, plus from Word or Access to Excel, too). It also works across networks and other computers. So if your colleague has a spreadsheet they update, and you need the latest calculation from them automatically, you can use this functionality.
There is a free add-in out there that has a way to do this, I think. Anyways, it is a good thing to promote - free and cool. It is called ASAP Utilities and can probably do/fix all of the questions asked so far.
A work-around that I have done in a similar situation like you describe is to use a big =IF() formula in the column next to the info that gets updated. Let’s say the info you have is in column B. Last month is in B20, this month in B21, next month will be put in B22 but right now B22 is blank. In Column C I would put a big =IF formula to only show the latest value in column B, like so: in C21 “=IF(B22=”",B21,"")" which says “If cell B22 is empty, then show me B21, but if B22 has any value in it, then show me nothing” which if copied into the whole range of associated cells in Column C would effectively only show you the bottom/latest value (B21). Then in, say, cell D1 I would put “=MAX(C1:C9999)”. So column C has a formula that returns a bunch of empty values, except in the one cell that is next to the latest information (now in B21)…thus it will be the maximum value in the column. Now you can point to D1 from anywhere else and it will always show the latest value.
If/then…well, you can always program a macro with if/then statements. But I suspect you want it in the spreadsheet itself. You can do an =IF formula, but it mainly works with cell values, not with just a column itself…but like in my above example, you could use =MAX(B1:B9999) in a cell (let’s use cell D1 again) to return the highest number in column B. Then =IF(D1>0,today(),"") which says “if D1 is greater than 0, then return today’s date, otherwise return blank.” Now that I think about it, I guess you could cut out the middle man with =IF(MAX(B1:B9999)>0,Today(),"")
=IF formula is quite handy, read up on it in the help of Excel.
Unfortunately, no direct way, and it’s a pain to have to do that every time. I used to use a macro to change that kind of thing, but I lost it when changing computers a while back. Hang on, let me re-create it …
Thanks for waiting. Excel can record macros, so I just made a chart, selected it, and then recorded what I did to it. Here’s the resulting macro:
Sub chartchange()
' this section sets the interior of the plot area to white
ActiveChart.PlotArea.Select
Selection.Interior.ColorIndex = xlNone
' this section sets the title to "Rockwell Extra Bold", and the font size to 14
ActiveChart.ChartTitle.Select
With Selection.Font
.Name = "Rockwell Extra Bold"
.Size = 14
End With
' this section sets the value axis title to "Palatino", and the font size to 12
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection.Font
.Name = "Palatino"
.Size = 12
End With
End Sub
Put this macro in your Personal Macro Workbook, and when you need to use it, remember to first select the chart you want it to apply to.
If you don’t know how to use macros, you really should learn. Much of the power of excel resides in the macros. Maybe someone on the list can suggest a tutorial, I kinda taught myself over the years.
There are a variety of functions in excel. To create an complex number of the form
3+4i
use the function
=complex(3,4)
Other functions include
IMAGINARY - returns the imaginary part of the complex number
IMSUM - returns the sum of complex numbers
IMSQRT - returns the square root of a complex number
Do a search in Help for “Imaginary”. Note that if this function is not available, and returns the #NAME? error, you’ll need to install and load the Analysis ToolPak add-in.
But I have to create the names by hand in each individual sheet and type in (or do a global replace) the names in the summary sheet. Each step is slow and error prone.