Tag Info

Hot answers tagged

15

The logical operations are represented by formulae: AND( condition1 , condition2 , ... ) OR( condition1 , condition2 , ... ) NOT( condition ) Each condition can be pretty much anything with a logical evaluation, meaning you can nest logical operations by nesting the formulae as required. So in your case you need: =IF( AND( myval>=minval , myval ...


14

I think the closest you can get from native Excel functionality is Save As | Formatted Text (Space Delimited) (*.prn). It will automatically determine the widths and insert spaces to pad to that width as necessary. Beyond that you need to have a macro or other add-in that will let you do more.


10

Just set the cell to be shown in numerical format and it will show a decimal number. The integer part corresponds to the date value (i.e. the day) and the decimal part to the time of the day. If you multiply the numerical value of 1:45 (which is 0,07) by 24x60 (the number of hours in a day and the number of minutes in an hour) you will obtain the value in ...


10

Click cell you want to restrict value for. Go to Data->Validation On the dialog that comes up, for Allow, select List, and for Source, put in "Yes,No". Now your cell will have a dropdown with Yes and No. User can also type these values without clicking the dropdown button.


10

I have placed the data from "the first excel" on Sheet1, and "the 2nd excel" on Sheet2. The key to this solution is the VLOOKUP() function. First we insert a column. We then use the VLOOKUP() function to lookup the value of "1" in Sheet2. We specify 2 as the value of the third parameter, meaning we want the value of the 2nd column in the array. Also ...


9

This issue was discussed and answered on Microsoft Office for Developers Forums on April 14, 2011. Question posted by Vershner: I originally posted this in the IT Professionals forum but they told me to post it here because the issue was by design. When I click undo in the quick access toolbar it undoes the last action in Excel, not the ...


9

Insert a column In first row insert formula =MOD(ROW(),7) Copy down Copy/paste special/values Data/Filter out the ones you want (0 or 6, probably) Delete the rest of the rows Remove filter Delete column


9

Short answer: There's no way to have a date without a year, it's simply a required part of a date. Just like the second is a required part of the time of day - if you enter, say, "1:15", the actual value will be "01:15:00" (or something like that, depending on your regional settings). The only thing you can do is format the column as text, use a date format ...


8

Some numbers are displayed in exponential format if the column is too narrow and you can fix this by increasing the width of the column. However, very large numbers in Excel are stored in floating point format and cannot be represented in Excel exactly. You may be able to override their display using cell formatting but the true values will still be stored ...


8

How about an If/then to check the value of D62? =IF(D62=0,0,100%-(D62/C62))


8

The way 'around' it is to open multiple copies of Excel, one per worksheet. This is really annoying, but there is no other way. I have special shortcuts for my main spreadsheets, just to open them explicitly. To do this: Make a NEW shortcut - right-click on your desktop, New, Short cut. Browse to the excel program ( "C:\Program Files (x86)\Microsoft ...


7

You need to create a new default template that will load at startup. They didn't make it easy to do in Office 2007! But here's how: Open a new blank worksheet. Insert > Hyperlink > Paste in any link Home > Styles > Right click on the 'Hyperlink style' > Modify > Set your font options You probably will want to do the same for the 'Followed Hyperlink' style ...


7

=TRUNC(A1)&":"&REPT("0",2-LEN(ROUND(60*(A1-TRUNC(A1)),0)))&ROUND(60*(A1-TRUNC(A1)),0) Where A1 is the decimal value.


6

This happens because of the limited precision of floating point numbers representation on computers and cannot be fixed because it is an intrinsic problem of the way number are represented on a computer. There might be workarounds, such as round() or a clever implementation of the formulas to reduce error propagations. Excel uses 8-byte (64 bit) floating ...


6

Select all the worksheets (select the first sheet, hold shift, select the last sheet) Type your name in A1 Hit Enter. This will do it for the non-code people.


5

If you have Office Professional, you can open your Excel file in Access, and then Export from Access. Access will let you specify a fixed-width layout for your exported file, and gives you extremely granular controls for specifying those widths.


5

Hit Esc or Enter I believe.


5

I think this will work: A1 6:23 B1 8:23 C1 =(B1-A1)*24*60 Just make sure you format C1 as "General"


5

iReport will not function correctly without a connection to JasperServer. If you're looking for easy reports your best tools are really excel and access. As far as end-user simplicity the buck stops there. If you're looking for something a bit more robust then I would take a look at any of the following for open source solutions. If you want to go ...


5

I agree, it is annoying. Best I've found is to treat the workbooks as "tabs", and use Ctrl + W to close only one. Or click the x close button, not on the very top but the lower one for the workbook.


5

I would suggest if you have that much data you need to consider moving to a database rather than a spreadsheet. If your version of Office came with MS Access, this is a reasonable solution for the immediate future. If your data continues to grow you'll eventually need to migrate to a different DB because Access eventually tops out as well (around 2GB of data ...


5

This will do what you want: Open the Registry Editor, browse to HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open Delete the ddeexec key, (or just rename it if you are worried - I just renamed it and it worked) Click on the "command" key and replace the /e on the end of the (Default) and command strings in that key with "%1" The quotes around %1 are important ...


5

Be aware that dates and times in Excel are stored internally as an amount of days. You can use this fact to simplify conversions of time amounts. If you have an amount of hours in A1 you can calculate an amount of days using =A1/24 then display it as hours and minutes using the custom number format [h]:mm. Follow these steps: In the desired cell, enter ...


5

Try Shift+Enter. This will keep your selection but move the active cell to the end of the selection.


5

The code is too clever for it's own good. It uses the Text property, which is what is displayed in a cell, to get a text representation of a cells value. The problem as mentioned in another answer is this is limited to 1024 characters. You can change the code to use the Value property but this might not work on all cell value types. Change this line: Print ...


5

Using OpenOffice is probably the easiest option IMO because there's no file format conversion necessary and OpenOffice is freely available. There is a BSD licensed Microsoft Office 2003 plugin that is available, OpenXML/ODF Translator Add-in for Office. I don't have Microsoft Office so I'm unable to try it.


5

When you open the csv, you'll have the option to specify the delimiter and data type for each column. The text import wizard has 3 steps. Note that my screen shots are from Excel 2010, but this will work in exactly the same manner in Excel 2003. Step 1: select delimited (instead of fixed width) Step 2: select comma as your delimiter Step 3: select each ...


4

Might need to use a few steps. You have the full date. In a column beside it, place the following formula. =CONCATENATE(MONTH(A1),"-",DAY(A1)) This will give you the month, a dash, the day. You may want to pretty it up, or use multiple columns. Then, copy all of the cells with the new month-day combination. In the next column, Paste Special, and ...


4

If the files are static (on your desktop, My Docs, etc) then you can reference this link: http://www.techonthenet.com/excel/questions/instance.php If you have random files (from the Internet, E-Mail, etc) then you can do this to change the way it opens all XLS files to open in a new instance: ...


4

Here's a quick step-by-step break down of what I've done, which I think achieves what you're after. First, prepare data and select the relevant columns: Create the Pivot Table, I've done so in a seperate sheet, it doesn't really matter where. Drag the Date field to the "Drop Row Fields Here" box on the left. Drag the Value field to the "Drop Data ...



Only top voted, non community-wiki answers of a minimum length are eligible