microsoft office spreadsheet component help€¦ · when you view and interact with a spreadsheet...

ShowAll

AboutspreadsheetsYoucanuseaspreadsheettoenterandcalculatedata.Aspreadsheetconsistsofcolumnsandrowsofcells.Youcanenterdatadirectlyintothecellsofthespreadsheetandperformcalculationsthatarebasedonthedatabyusingformulas.Youcanmakechangestothedataandseetheresultsofcalculationsautomatically.Youcanalsosort,filter,andfinddatainaspreadsheet.ThefollowingexampleshowsaspreadsheetonaWebpagedesignedtobeusedasamortgagecalculator.Whenyouchangethevaluesintheborderedinputcellsinthebrowser,thespreadsheetcalculatesthemortgagepayment.

Browserandlicenserequirements

Savingchanges

Disabledfeatures

ShowAll

BrowsersupportforspreadsheetsToworkwithinteractivespreadsheetsinMicrosoftInternetExplorer4.01andlater,youmusthavetheMicrosoftOfficeWebComponentsinstalledandanappropriateOfficeXPlicense.IfyoudonothaveanOfficeXPlicense,youcaninstallthecomponentsandviewthemonaWebpage,butyoucannotinteractwiththem.

FollowingarethelevelsofbrowsersupportavailablewhenyouhavetheOfficeWebComponentsinstalledandanOfficeXPlicense.

MicrosoftInternetExplorer5andlater

MicrosoftInternetExplorerversions4.01,4.1x,and4.2x

Otherbrowsers

AboutMicrosoftOfficeWebComponentsMicrosoftOfficeWebComponentsisasetofActiveXcontrolsinstalledwithMicrosoftOfficeXP.WithOfficeWebComponentsinstalledonyourcomputer,youcaninteractivelyuseaspreadsheet,aPivotTablelist,andachartonaWebpageinMicrosoftInternetExplorer4.01orlater.IfyouwanttousethecomponentsonadataaccesspageinMicrosoftAccess,youshouldhaveInternetExplorer5orlater.

IfOfficeWebComponentsisnotinstalledonyourcomputerbutyouhaveanOfficeXPsitelicensethatpermitsintranetdistribution,youcanconfigurethecomponentssothatuserswillbepromptedtodownloadOfficeWebComponentsfromyourcorporateintranet.ThisallowsusersatyoursitetouseadataaccesspagewithouthavingOfficeXPsoftwareinstalledontheircomputers.

ForinformationabouthowtoconfigureOfficeWebComponents,seetheMicrosoftOfficeXPResourceKit.

AboutView-onlymodeforOfficeWebComponentsIfyoudonothaveaMicrosoftOfficeXPsoftwarelicenseinstalledonyourcomputeroraccessedviaaMicrosoftOfficeXPapplication,youcanviewaMicrosoftOfficeWebComponent—aSpreadsheetComponent,ChartComponent,orPivotTableComponent—onaWebpage,butyoucan'tinteractwithit.

Tobeabletointeractwithandusefullfunctionalityofthecomponentyouareviewing,youmustinstallMicrosoftOfficeXPorhaveaccesstoanOfficeXPlicense.Ifyouareinacorporateorgroupenvironment,checkwithyournetworkadministratorforassistance.

WhenyouareinView-onlymode,youcan:

Viewdata.

Print.

Selectandscroll.

Usehyperlinks.

Resizeacomponent.

SelectsheetsintheSpreadsheetComponent.

ExpandorcollapsemembersinaPivotTablelist.

RefreshdatainaPivotTablelist.

UsetheAbout,Help,andRefreshcommands.Helptopicsthatdealwithinteractivity(suchasformattingcells,draggingfields,sorting,filtering,andsoon)donotapply.

AllcommandsexceptAbout,Help,andRefreshareunavailableinView-onlymode.

ShowAll

AboutgettingdesignhelpforspreadsheetsWhenyouviewandinteractwithaspreadsheetinyourWebbrowser,youareworkingwithacomponentthatwascreatedinanotherprogramcalledadesignprogram.InadesignprogramsuchasMicrosoftFrontPageordataaccesspagesinMicrosoftAccess,youcanaddthedatathatyouwantinthespreadsheet,addthespreadsheettoaWebpageyou'redesigning,andpublishtheWebpagetoaWebserver.Helpaboutdesigningaspreadsheetisavailablefromwithinthedesignprogram.

Tolearnmoreaboutdesigningaspreadsheet,firstchooseadesignprogramtoworkin.Youcanstartinanyofthesupporteddesignprograms,andthenlaterworkonthesamespreadsheetinadifferentdesignprogramifyouneeddifferentdesignfeatures.

Gettinghelpindesignprograms

Gettinghelpwithwritingscriptsandprogramsthatusespreadsheets

ShowAll

KeyboardshortcutsinspreadsheetsForinformationaboutthekeystoactivateanddeactivatetheMicrosoftOfficeSpreadsheetComponentonthepage,seeHelpinyourdesignprogram.

Keysformovingandscrollinginaspreadsheet

KeysformovingwithSCROLLLOCKon

Keysforworkingwithsheets

Keysforenteringdataonasheet

Keysforworkingincells

Keysforformattingdata

Keysforeditingdata

Keysforinserting,deleting,andcopyingaselection

Keysformovingwithinaselection

Keysforselectingcells,columns,orrows

KeysforworkingwithAutoFilterdrop-downlists

KeysforworkingwithHelp

ShowAll

Aboutspreadsheettools,commands,andoptionsToolbar

CommandsandOptionsdialogbox

ShoworhidetheCommandsandOptionsdialogboxDooneofthefollowing:

ToshowtheCommandsandOptionsdialogbox,clickCommandsandOptions onthespreadsheettoolbar.

TohidetheCommandsandOptionsdialogbox,clickClose intheupperrightcornerofthedialogbox.

Addorremovesheets1. ClickCommandsandOptions onthespreadsheettoolbar,

andthenclicktheWorkbooktab.

2. UnderWorksheets,dooneofthefollowing:Toaddasheet,clickInsert.Repeattoaddmultiplesheets.

YoucanchangethenameofaninsertedsheetbyselectingthenameintheSheetnamelist,typinganewnameintheSheetnamebox,andpressingENTER.

Toremoveasheet,clickthenameofthesheetintheSheetnamelist,andthenclickDelete.

Thesheetanditsdataarepermanentlydeleted.

Changethenameofasheet1. Onthespreadsheettoolbar,clickCommandsandOptions ,


2. UnderWorksheets,intheSheetnamelist,clickthenameofthesheetyouwanttochange.

3. TypethenewnameintheSheetnamebox.

4. PressENTER.

Changetheorderofsheets1. Onthespreadsheettoolbar,clickCommandsandOptions ,


2. UnderWorksheets,intheSheetnamelist,clickthenameofasheetyouwanttomove.

3. NexttoOrder,clickUp tomovethesheettowardthebeginningoftheworkbook,orDown tomovethesheettowardtheendoftheworkbook.

ShoworhidecolumnheadersorrowheadersColumnheadersarethelettersingraycells(orincellsofanothercolor,dependingonyourMicrosoftWindowsdisplaysettings)acrossthetopofthespreadsheet,androwheadersarethenumbersingraycellsdownthesideofthespreadsheet.

1. Onthespreadsheettoolbar,clickCommandsandOptions ,andthenclicktheSheettab.

2. UnderShow/Hide,selectorcleartheColumnheadersorRowheaderscheckboxes.

Showorhidegridlinesinaspreadsheet1. Onthespreadsheettoolbar,clickCommandsandOptions ,

andthenclicktheSheettab.

2. UnderShow/Hide,selectorcleartheGridlinescheckbox.

ShoworhidethesheetselectorThesheetselectorconsistsoftheSheetNametabandtheSheetlist,thesecondofwhichappearswhenyouclickthearrowonthetab.

1. Onthespreadsheettoolbar,clickCommandsandOptions ,andthenclicktheWorkbooktab.

2. UnderShow/Hide,selectorcleartheSheetselectorcheckbox.

SavechangesChangesyoumaketoaspreadsheetinthebrowserareavailableduringthecurrentbrowsersessiononly.Tosavechangestodata,dooneofthefollowing:

Onthespreadsheettoolbar,clickExporttoMicrosoftExcel andsaveacopyofthespreadsheetasaMicrosoftExcelworksheet.

CopythespreadsheetdatatoExceloranotherprogramonyourcomputer,andthensavethedatainthatprogram.

UndochangesToundothelastchangeyoumadeinthespreadsheet,clickUndo onthetoolbar.Youcanundomultiplechanges(upto16)byclickingUndomorethanonce.

Changesyoumakeinthebrowserarenotsaved.Toundoallchangesmadeduringthecurrentsession,refreshtheWebpagebyclickingRefresh onthebrowsertoolbar.

PrintaspreadsheetToprintaspreadsheetfromthebrowser,youmustprinttheentireWebpage.

InMicrosoftInternetExplorer,clickPrintontheFilemenu.

NoteIfthespreadsheetiswiderorlongerthantheprintedpage,theareasthatdon'tfitonthepagearenotprinted.Formorecontroloverhowthedataisprinted,includingtheabilitytoprintalonglistandsetpagebreaks,youcanexportthespreadsheettoMicrosoftExcelbyclickingExporttoMicrosoftExcel onthetoolbar.ForinformationaboutprintingfromExcel,seeExcelHelp.

ShowAll

Aboutenteringtext,numbers,dates,andtimesinaspreadsheetEnteringtext

Enteringnumbers

Enteringdatesandtimes

Entertext,numbers,dates,andtimesinaspreadsheet1. Clickthecellwhereyouwanttoenterdata.

2. Typethedata,andthenpressENTERortheTABkey.

Notes

Toseparatethepartsofadate,useaslashorahyphen;forexample,type9/5/2002or5-Sep-2002.

Toenteratimebasedonthe12-hourclock,typeaspaceandthentypeAMorPMafterthetime;forexample,type9:00PM.Otherwise,thespreadsheetentersthetimeasAM.

Precedeformulaswithanequal(=)sign.

Toenterahyperlink,typetheURLaddress,beginningwithaprotocolsuchashttp://orftp://,andthenpressENTER.

Toenterthesamedatainseveralcells,selectthecells,typethedata,andthenpressCTRL+ENTER.

Howaspreadsheethandlesdatesintheyear2000andbeyondBecausetherulesthatgovernthewayanycalculationprograminterpretsdatesarecomplex,youshouldbeasspecificaspossibleaboutdateswheneveryouenterthem.Thiswillproducethehighestlevelofaccuracyinyourdatecalculations.

HowaspreadsheetinterpretsambiguousdatesWheninterpretingambiguousdates,thespreadsheetmakescertainassumptions.Forexample,whenyouenteradatethatincludesonlythemonthandoneortwodigits,thespreadsheetassumesthat1through31isthedayandthattheyearisthecurrentyear.Forinstance,thespreadsheetassumesthatDecember01isDecember1ofthecurrentyear,notDecemberoftheyear2001.ThespreadsheetassumesthatDecember32isDecember1,1932.

Howaspreadsheetinterpretstwo-digityearsBydefault,whenyouenteratwo-digityearvalue,thespreadsheetinterpretstheyearasfollows:

00through29Thespreadsheetinterpretsthetwo-digityearvalues00through29astheyears2000through2029.Forexample,ifyoutypethedate5/28/19,thespreadsheetassumesthedateisMay28,2019.

30through99Thespreadsheetinterpretsthetwo-digityearvalues30through99astheyears1930through1999.Forexample,ifyoutypethedate5/28/98,thespreadsheetassumesthedateisMay28,1998.

Usefour-digityearsToensurethatthespreadsheetinterpretsyearvaluesthewayyouintended,typeyearvaluesasfourdigits(forexample,2001,ratherthan01).

ChangehowyearsareinterpretedYoursystemadministratorcancustomizeMicrosoftOfficesothatdifferentrulesdeterminehowdatesareinterpreted.Forexample,youradministratorcouldchangetheearliesttwo-digityearvaluethat'sinterpretedasadateinthetwentiethcenturyfrom30toanothernumber.Also,administratorscanchangethedefaultdateformattodisplayfour-digityearsinsteadoftwo-digityears.

Tofindoutifyoursystemusescustomizeddateinterpretation,seeyour

administrator.Forinformationabouthowtocustomizedateinterpretation,seetheMicrosoftOfficeXPResourceKit.

Changedatainacell1. Clickthecellyouwanttochange.

2. Dooneofthefollowing:

Toenteranewnumberortextinthecell,replacingthecurrentcontentsofthecell,typethenewvalue.

Tochangeaportionofthecontentsofthecell,double-clickthecellandmakeyourchanges.

3. Toacceptyourchangestothecell,pressENTER.

NoteAcellcanhaveonlyonefontandtextformat.

ShowAll

AboutmovingandcopyingdataWhenyoucopyacell,youcopytheentirecell,includingformulasandtheirresultingvalues,andcellformats.Cellsthatyoucopyormovereplaceanydataintheareawhereyoupastethecells.

Copyingandmovingaffectsformulas

Filteringaffectscopyingandmoving

Copyingtootherprograms

CopyingintheSpreadsheetComponentdiffersfromMicrosoftExcel

ShowAll

MoveandcopydataMoveorcopywholecells

Moveorcopycharacterswithinacell

ShowAll

InsertordeleterowsorcolumnsInsertrowsorcolumns

Deleterowsorcolumns

ShowAll

Insert,change,ordeleteahyperlinkInsertahyperlink

Changeahyperlink

Deleteahyperlink

ShowAll

Clearcellcontents1. Selectthecellsthatyouwanttoclear.

2. PressDELETE.

Tip

ShowAll

AboutformulasWhatisaformula?

Calculation

Constantsvs.cellreferences

Functions

ShowAll

ExamplesofcommonlyusedformulasCreateasumbasedononecondition

Counttheoccurrencesofavalueoracondition

Calculatearunningbalance

Joinfirstandlastnames

Joinadatewithtext

Increaseanumberbyapercentage

ShowAll

Createaformula1. Clickthecellinwhichyouwanttoaddaformula.

2. Typeanequalsign(=).

3. Typetheformula.

4. PressENTER.

Tip

Changeaformula1. Double-clickthecellthatcontainstheformulayouwantto

changesothattheformulaappearsinthecell.

Ifthecellcontainsahyperlink,clickacellnexttothecellyouwanttochange,andthenuseanarrowkeytoselectthecellwiththeformulayouwanttochange.ThenpressF2.

2. Makechangestotheformulainthecell.

3. PressENTER.

Viewaformulaanditsresultatthesametime1. Clickthecellthatcontainstheformulayouwanttoview.

2. Onthespreadsheettoolbar,clickCommandsandOptions ,andthenclicktheFormulatab.

3. ReviewtheformulaintheFormulainactivecell(reference)box.TheresultoftheformulaisdisplayedintheCellvaluebox.

InsertatotalforarangeYoucaninsertasumforarangeofcellsautomaticallybyusingAutoSum.

1. Clickthefirstblankcellthatfollowsthedatayouwanttosum,eitherbelowthecolumnofdataortotherightofthedatainarow,orselectacolumnrangetosum.

2. Onthespreadsheettoolbar,clickAutoSum .

Asuggestedformulaisdisplayedinthecellyouselectedorinthecellbelowthecolumnrangeyouselected.


Toacceptthesuggestedformula,pressENTER.

Tochangethesuggestedformula,selecttherangeyouwanttosumandpressENTER.

ChangewhenaspreadsheetcalculatesWhenaspreadsheetcontainsmanyformulas,itmighttakealongtimetocalculate.Tospeedupyourwork,youcanchangethecalculationmodefromautomatictomanualsothatyoucandecidewhentorecalculatethespreadsheet.

1. Onthespreadsheettoolbar,clickCommandsandOptions ,andthenclicktheWorkbooktab.

2. UnderCalculation,dooneofthefollowing:

Torecalculatethespreadsheetwheneveravaluechanges,selectAutomatic.

TorecalculatethespreadsheetonlywhenyoupressF9orclickCalculate,selectManual.

ShowAll

AboutusingnamesinformulasOverview

Guidelines

Definenamesforcells,formulas,orconstants1. Onthespreadsheettoolbar,clickCommandsandOptions ,

andthenclicktheFormulatab.

2. UnderNames,typethenameyouwanttouseintheNamebox.

Ifyouwanttodefineanamethatisavailabletoonlyonesheet(alocalname),precedethenameyouwanttousebythesheetnamefollowedbyanexclamationpoint.Forexample,ifyouwanttonameacellBudget,andmakeitavailableonlyonSheet2,typethefollowingintheNamebox:

Sheet2!Budget

3. IntheReferstobox,type=(equalsign),followedbytheformula,constantvalue,orreferencetothecellorrangeofcellsyouwanttoname.Forexample,tonamecellsA1throughA5onSheet1,typethefollowing:

=Sheet1!$A$1:$A$5

4. ClickDefine.

Changeordeleteadefinedname1. Onthespreadsheettoolbar,clickCommandsandOptions ,

andthenclicktheFormulatab.

2. UnderNames,intheAllnamesbox,clickthenameyouwanttochangeorremove.


Tochangethename,typeanewnameintheNameboxandclickDefine.Then,clicktheoriginalnameintheAllnamesboxandclickRemove.

Tochangethecell,formula,orconstantrepresentedbyaname,changeitintheReferstobox,andthenclickDefine.

Todeletethename,clickRemove.

ShowAll

AboutcellandrangereferencesOverview

Relativeandabsolutereferences

AboutcalculationCalculationistheprocessthataspreadsheetusestocomputeformulasinitscellsandthendisplaytheresultsasvalues.Bydefault,formulasareautomaticallycalculatedwhenachangeoccursinthecellsthattheformulasreferto.However,youcanchangehowaspreadsheetcalculates.

Storedvs.displayedvaluesValuesaresometimesdisplayedandstoreddifferently.Datesandtimes,forexample,arestoredasserialnumbersbutareusuallydisplayedandprintedinoneofseveraldateortimeformats.Thespreadsheetusesthestoredserialnumbersincalculations,whichmakesitpossibletocalculatethenumberofdaysbetweentwodates.

Theformatthatstoredvaluesaredisplayedinsometimesdependsonhowyouchoosetoformatthem.Forexample,youcanformatacellthatcontainsthenumber123456789todisplayas1.23E+08byusingtheScientificformat.Oryoucanformatacellthatcontainsadatewiththeserialnumber36699todisplayas"6/22/2000"oras"22-Jun-2000".Changingthedisplayofavaluedoesnotchangethestoredvalue.

TheregionalsettingsinMicrosoftWindowsControlPanelalsodeterminehownumber,currency,date,andtimeformatsaredisplayed.Forexample,thesesettingsdeterminewhetheryearsaredisplayedwithtwodigitsorfourdigits.SeeWindowsHelpforinformationabouttheseformats.

ShowAll

AboutcalculationoperatorsOperatorsspecifythetypeofcalculationthatyouwanttoperformontheelementsofaformula.TheSpreadsheetComponentincludesfourtypesofcalculationoperators:arithmetic,comparison,textconcatenation,andreference.Operatorscalculateinaspecificorder.

Typesofoperators

Orderinwhichoperatorscalculate

AboutcreatingaformulathatusesdataonthesameWebpageYoucanreturnavalueinacellfromdatathat'sonthesameWebpageasyourspreadsheetbyusingtheSpreadsheetComponentHOSTfunction.Forexample,ifyouhaveatextboxcontrolonyourpagethat'snamedTextBox1,thefollowingformulawillretrievethevaluefromthetextboxanddisplayitinthecellthatcontainstheformula:

=HOST().textbox1.value

YoucanalsocreateformulasthatperformcalculationsonvaluesreturnedfromtheWebpage.Forexample,ifthetextboxyou'rereturningavaluefromcontainsasalesamount,andcellC1onthespreadsheetcontainsacommissionrate,youcouldmultiplythevaluereturnedfromtheWebpagewiththevalueinthecommissionratecell:

=$C$1*HOST().textbox1.value

YoucanalsoreferenceavalueinanotherspreadsheetonthesameWebpage.Forexample,ifyouhavespreadsheet1andspreadsheet2onaWebpage,thefollowingformulainspreadsheet1returnsthevaluefromcellA1inspreadsheet2:

=HOST().spreadsheet2.range("a1").value

Formoreinformationaboutthefunctions,controls,andpropertiesyoucanuseonaspreadsheet,seehowtoaccessprogrammingHelptopicsforspreadsheets.

ShowAll

AboutarrayformulasandarrayconstantsAnarrayformulacanperformmultiplecalculationsandthenreturneitherasingleresultormultipleresults.Arrayformulasactontwoormoresetsofvaluesknownasarrayarguments.Eacharrayargumentmusthavethesamenumberofrowsandcolumns.Youcreatearrayformulasinthesamewaythatyoucreateotherformulas,exceptyoupressCTRL+SHIFT+ENTERtoentertheformula.Whenyou'reeditinganarrayformula,theformulaappearsinitalicformatting.

Arrayconstantscanbeusedinplaceofreferenceswhenyoudon'twanttoentereachconstantvalueinaseparatecellontheworksheet.

Someofthebuilt-infunctionsarearrayformulas,andmustbeenteredasarraystogetthecorrectresults.

Usingarrayformulas

Usingarrayconstants

ShowAll

CreateanarrayformulaCalculateasingleresult

Calculatemultipleresults

ShowAll

EditanarrayformulaIfanarrayformulaoccupiesmultiplecells,youmustedittheentirerangeasthoughitisasinglecell.Youcannotchangejustoneelementofanarrayformula.

1. Selectthecellthatcontainsthearrayformula,orifthearrayformulaoccupiesmultiplecells,selecttherangethatcontainsthearrayformula.

How?

""

2. PressF2.

3. Makechangestothearrayformula.

4. PressCTRL+SHIFT+ENTER.

Selectanarrayformularange1. Selectacellwithinthearray.

2. PressCTRL+/(forwardslash).

ShowAll

Moveorcopyanarrayformula1. Selecttheentirearrayrange.

How?

""

ClickCut orCopy onthespreadsheettoolbar.

2. Selecttheentiredestinationrange;otherwise,thearraywillbepastedintoasinglecell.

3. ClickPaste .

Aboutusingfunctions

SpreadsheetfunctionsFunctionsarepredefinedformulasthatperformcalculationsbyusingspecificvalues,calledarguments,inaparticularorder,orstructure.Forexample,theSUMfunctiontotalsvaluesorrangesofcells,andthePMTfunctioncalculatestheloanpaymentsbasedonaninterestrate,thelengthoftheloan,andtheprincipalamountoftheloan.Seealistofavailablespreadsheetfunctions.

ArgumentsArgumentscanbenumbers,text,logicalvaluessuchasTRUEorFALSE,errorvaluessuchas#N/A,cellreferences,constants,formulas,orotherfunctions.Eachargumentyoudesignatemustproduceavalidvalueforthatargument.

StructureThestructureofafunctionbeginswiththefunctionname,followedbyanopeningparenthesis,theargumentsforthefunctionseparatedbycommas,andaclosingparenthesis.Ifthefunctionstartsaformula,typeanequalsign(=)beforethefunctionname.

mk:@MSITStore:owcfun10.chm::/html/wxrefFuncCategories.htm

NestedfunctionsIncertaincases,youmightneedtouseafunctionasoneoftheargumentsofanotherfunction.Forexample,thefollowingformulausesanestedAVERAGEfunctionandcomparestheresultwiththevalue50.

ValidreturnsWhenanestedfunctionisusedasanargument,itmustreturnthesametypeofvaluethattheargumentuses.Forexample,iftheargumentreturnsaTRUEorFALSEvalue,thenestedfunctionmustreturnTRUEorFALSE.Ifitdoesn't,thespreadsheetdisplaysa#VALUE!errorvalue.

ShowAll

Formatcellsinaspreadsheet1. Selectthecellsyouwanttoformat.

2. Onthespreadsheettoolbar,clickCommandsandOptions ,andthenclicktheFormattab.

3. Dooneormoreofthefollowing:

Changetextfontorsize

Changeanumberformat

Placebordersaroundcells

Changethebackgroundcolorofcells

Aligndatainacell

NumberformatsYoucanchangetheformatsofnumbersinaspreadsheetbyusingtheCommandsandOptionsdialogbox,bothinthedesignprogramandatruntimeinthebrowser.ThesettingsinMicrosoftWindowsControlPanelalsodeterminehownumber,currency,date,andtimeformatsaredisplayed.Forexample,thesesettingsdeterminewhetheryearsaredisplayedwithtwodigitsorfourdigits.SeeWindowsHelpforinformationabouttheseformats.

InMicrosoftVisualBasicorascript,youcanassigncustomnumberformatstothedata.YoucanalsotypecustomformatsavailableinthespreadsheetobjectmodelintheNumberformatbox.Tofindoutmore,seehowtogethelpforprogrammingaspreadsheet.

ThefollowingnumberformatsareavailableintheNumberformatlistontheFormattaboftheCommandsandOptionsdialogbox.

Format EffectsGeneraldate Displaysanumberasadateandoptionaltime.

Forexample,8/31/9905:54AM.

Thefractionalpartofthenumberisthetime.Ifanumberhasnofractionalpart,onlyadateappears.Ifanumberhasonlyafractionalpartandnointegerpart,onlyatimeappears.

Longdate DisplaysadateaccordingtotheWindowsregionalsettingforthelongdateformat.Forexample,Thursday,January25,2001.

Mediumdate DisplaysadateaccordingtotheWindowsregionalsettingforthemonthanddateseparator,anddisplaystwodigitsfortheyear.Forexample,Jan/25/01.

Shortdate DisplaysadateaccordingtotheWindowsregionalsettingfortheshortdateformat.Forexample,1/25/01.

Longtime DisplaysatimeaccordingtotheWindowsregionalsettingforthelongtimeformat.For

example,8:45:36PM.Mediumtime Displaysatimein12-hourformatwithanAMor

PMdesignator,omittingtheseconds.Forexample,8:45PM.

Shorttime Displaysatimein24-hourformat,omittingtheseconds.Forexample,20:45.

General Displaysanumberright-aligned,withnothousandsseparator,andprecededbyaminussignifnegative.Displaysupto11digits,or10digitswithadecimalpoint,roundingadditionaldigitstotherightofthedecimalpoint.Displaysnumberswithmorethan11digitstotheleftofthedecimalpointinscientificformat.Displaystextleft-aligned.

Currency DisplaysanumberaccordingtotheWindowsregionalsettingforcurrency,withathousandsseparatorifappropriateandtwodecimalplaces.Forexample,withthedefaultEnglish(UnitedStates)regionalsettings,$2,532.75.

Eurocurrency Displaysanumberaseurocurrency,withathousandsseparatorandtwodecimalplaces.TheeurosignisplacedbeforeorafterthenumberaccordingtotheWindowsregionalsettingforcurrency.Forexample,withthedefaultEnglish(UnitedStates)regionalsetting, 12.35.

Fixed Displaysanumberwithtwodecimalplaces.Forexample,68.30.

Standard Displaysanumberwithathousandsseparatorandtwodigitstotherightofthedecimalpoint.TheWindowsregionalsettingfornumbersdetermineswhat'susedforthethousandsseparator.Forexample,withacommaasthethousandsseparator,1,800.00.

Percent Displaysanumbermultipliedby100,withtwodecimalplacesandapercentsign.Forexample,thevalue0.8914wouldbedisplayedas89.14%.

Scientific Displaysanumberinexponentialnotation.Forexample,1.25E+10.

Yes/no DisplaysNoifthenumberis0;displaysYesforanyothernumber.

True/false DisplaysFalseifthenumberis0;displaysTrueforanyothernumber.

On/off DisplaysOffifthenumberis0;displaysOnforanyothernumber.

Mergeorunmergecells1. Selectthecellsthatyouwanttomerge.Keepinmindthatwhen

youmergecells,onlydataintheupper-leftcelloftheselectedrangeisretained.Datainallothercellsthatyoumergeisdeleted.

2. Onthetoolbar,clickCommandsandOptions ,andthenclicktheFormattab.

3. UnderCellformat,clickMergeCells orUnmergeCells .

ShowAll

Changethewidthofacolumn1. Makesurecolumnheadersaredisplayed.

How?""

2. Selectthecolumnsyouwanttochange.

3. Pointtotherightboundaryofthecolumnheaderofthecolumnorselectionuntilthepointerchangesto .

4. Dragtheboundarytothewidthyouwant,or,tomakethecolumnwidthfitthecontents,double-clicktherightcolumnboundary.

ShowAll

Changetheheightofarow1. Makesurerownumbersaredisplayedonthespreadsheet.

How?""

2. Selecttherowsyouwanttochange.

3. Pointtothebottomboundaryoftherowheaderoftheroworselectionuntilthepointerchangesto .

4. Dragtheboundaryuntiltheroworselectionistheheightyouwant,or,tomaketherowheightfitthecontents,double-clickthebottomrowboundary.

ShowAll

DisplaytextoraspreadsheetsothatitreadsrighttoleftorlefttorightUseright-to-lefttextdirectionordisplaywhenyourspreadsheetwillbeviewedinlanguagesthatreadfromrighttoleft.

Displayanentirespreadsheetrighttoleftorlefttoright

Displaytextrighttoleftorlefttoright

AboutfindingdataTheFindwhatfeature(CommandsandOptionsdialogbox,Sheettab)canlocatetext,numbers,dates,andtimesthathavebeenentereddirectlyintocellsorthataretheresultsofformulas.

KeepthefollowingguidelinesinmindwhenusingFindwhatoptions:

MatchcaseBydefault,Findwhatisnotcasesensitive.Tofindonlythosecellswithvaluesthatmatchthecaseofyourentry,selecttheMatchcasecheckbox.

EntirecellonlyBydefault,FindwhatsearchesforcellsthatincludethevalueyouenterintheFindwhatbox.Forexample,anentryof"John"findscellswithstringssuchas"Johnson"or"LittleJohn."Tofindcellsthatcontainonlythevalueyouenteredandnothingelse,selecttheEntirecellonlycheckbox.

FormattedvaluesTheFindwhatfeaturesearchesfordisplayedvalues,notformats.Forexample,supposeacellisformattedwiththeYes/Nonumberformat.Ifthecellcontainsazero(0),thecelldisplaysthewordNo,andyoucanuseFindwhattolocatethephrase"No".Youcan'tuseFindwhattofindallcellsformattedwithaparticularcolororfont.

SearchdirectionTheFindwhatfeaturesearchesfromlefttorightacrossthecolumnsofthefirstrow,andthenfromlefttorightacrossthecolumnsofthesecondrow,andsoon.

WildcardcharactersTheFindwhatfeaturedoesnotallowwildcardcharacterssuchasquestionmark(?)andasterisk(*).

HiddendataTheFindwhatfeaturedoesnotfinddatainhiddencellsorcellshiddenbyfiltering.

Finddata1. Tosearchtheentirespreadsheet,clickasinglecell,or,to

searchwithinaspecificrangeofcells,selecttherange.

2. Onthetoolbar,clickCommandsandOptions ,andthenclicktheSheettab.

3. IntheFindwhatbox,typethetextorvalueyouwanttofind.

Notes

Bydefault,FindwhatsearchesforcellsthatincludethevalueyouenterintheFindwhatbox.Forexample,anentryof"John"findscellswithstringssuchas"Johnson"or"LittleJohn."Ifyouwanttofindcellsthatcontainonlythevalueyouenteredandnothingelse,selecttheEntirecellonlycheckbox.

Bydefault,Findwhatisnotcasesensitive.Ifyouwanttofindonlythosecellswithvaluesthatmatchthecaseofyourentry,selecttheMatchcasecheckbox.

4. ClickFindNext.

ShowAll

SortdataDatathatyouwanttosortshouldbesetupasalist.Alisthassimilaritemsineachcolumn,haslabelsforthecolumnsacrossthetoprow,anddoesnotincludeanyblankrowsorcolumns.

Sortbyasinglecolumn

Sortbymorethanonecolumn

ShowAll

ApplyorremoveafilterApplyafilter

Removeafilter

ShowAll

Sumfiltereddata1. Filterthedatatodisplaytherowsyouwanttosum.

How?""

2. Clickthecellbelowthedatayouwanttosum.

3. ClickAutoSum onthespreadsheettoolbar,andthenpressENTER.

NoteTheAutoSumbuttonsumsonlythedatainvisiblerows.Toincludeallrowsinthesum,showallrowsandthensumtherows.

AboutspreadsheetspublishedfromExcelWhenaspreadsheethasbeenpublishedfromMicrosoftExcel,mostExcelfeaturesareretainedandworkthesamewayasinExcel.FeaturesinthefollowinglistwillworkdifferentlyorwillnotberetainedwhenpublishedorsavedasaspreadsheetonaWebpage.

Excelformulas,functions,andcalculationfeatures

WhathappenswhenpublishedorsavedasaWebpage

Labelsinformulas Labelsareconvertedtocellreferences.Referencestodataonotherworksheets

Referencesareconvertedtovalues.

Subtotals Numbersandcalculationsappearcorrectly.Groupandoutlinefeaturesarenotretained.

1904datesystem Thedisplayeddatesremainthesame,buttheserialnumbersforthedatesareconvertedtothe1900datesystem.

Precisionasdisplayed Cellvalueispublished.R1C1referencestyle R1C1referencestyleisconvertedtoA1

referencestyle.

Excelformatting,graphics,anddisplayfeatures


Rotatedorverticaltext Rotatedorverticaltextisconvertedtohorizontaltext.

Distributedalignment Notretained.Thaialignment Notretained.Indentedtext Notretained.Wrappedtextincells Notretained.Graphics Notretained.Patternfills Notretained.

Dottedorbrokenborders Dottedorbrokenbordersareconvertedtosolidborders.

Multiplefontsinasinglecell Notretained.Thefontofthefirstcharacterinthecellisused.

Conditionalformatting Notretained.Cellcomments Notretained.Datavalidationrestrictionsandmessages

Notretained.

Auditingtracerarrows Notretained.Outlining Collapsedrowsarepublishedashidden.

Expandedrowsarepublishednormally.Printingorpagesetupfeatures

Notretained.

Excelfile,sharing,andimportfeatures


Sharedworkbookinformation

Notretained.

Lotuscompatibility R1C1referencestyleisconvertedtoA1referencestyle.

Externaldataranges Valuesarepublished.Theabilitytorefreshfromthesourcedataisnotretained.

Webqueries Valuesarepublished.Theabilitytorefreshfromthesourcedataisnotretained.

Passwords Notretained.

ExportaspreadsheettoanExcelworkbookOnthespreadsheettoolbar,clickExporttoMicrosoftExcel torunorswitchtoMicrosoftExcelanddisplaythedatainaworkbook.

Notes

BecauseExcelsupports256columnsand65,536rows,dataincolumnsIWthroughZZZandrows65,537through262,144arenotexportedfromthespreadsheet.Youcan,however,copythisdatatomorethanoneExcelworksheet.

WhenyouexportdatafromaspreadsheettoExcel,onlythevaluesandformulasareexported.LinksthatconnecttootherdataontheWebpageortootherWebpagesarenotretainedinExcel.

Whenyouexportsomespreadsheetnumberformats,thedatainExcelisdisplayedwithacustomnumberformat.Forexample,theYes/no,True/false,andOn/offspreadsheetformatsaredisplayedwithcustomnumberformatsinExcel.ForinformationaboutcustomnumberformatsinExcel,seeExcelHelp.

ShowAll

Troubleshootspreadsheets

EnteringandeditingdataandformulasIcan'tchangecellsinthespreadsheet.

Ican'twraptextwithincellsinaspreadsheet.

Thereisnoformulabarinmyspreadsheet.

Ican'tchangeahyperlink.

Ican'tcontrolthesizeofmyspreadsheet.

Ican'tcontrolwhetherscrollbarsaredisplayedorhiddeninmyspreadsheet.

Filtering,sorting,andfindingdataDatadoesnotsortcorrectlyinaspreadsheet.

Myspreadsheetlistdoesnotshowallofmydata.

Ihavemergedcells,andIcan'tfiltermyspreadsheet.

TheFindNextcommandisnotfindingdatainmyspreadsheet.

IpublishedaspreadsheetfromExcel,andtheAutoFilterdrop-downlistiswrong.

ImportingandexportingIcan'tpublishapassword-protectedspreadsheetfromExcel.

MyspreadsheetfilterisnotexportingtoExcelcorrectly.

IgetamessagethattherearetoomanyrowsorcolumnswhenItrytoexportmyspreadsheettoExcel.

WorkingwiththespreadsheetinthebrowserIcannotinteractwiththespreadsheet.

WhenIopenaWebpagethatcontainsaspreadsheetinthebrowser,Ireceivetheerror"CannotopenURL."

ClickingtheEditbuttoninmyWebbrowseropensmyWebpageinthewrongprogram.

Errorvalues

Iseeerrorvaluesinsteadofresultsforformulas.

ShowAll

TroubleshooterrorvaluesinaspreadsheetIfaformulacannotproperlyevaluatearesult,thespreadsheetdisplaysanerrorvalue.Forexample,errorvaluescanresultwhenaformulaexpectsanumericvaluebutfindstext,whenacellthatisreferencedbyaformulahasbeendeleted,orwhenthecellisnotwideenoughtodisplaytheresult.

Errorvaluesmightnotbecausedbytheformulaitself.Forexample,ifaformulashows#N/Aor#VALUE!,acellreferencedbytheformulamightcontaintheerror.

Followingarepossiblecausesandsolutionsforeacherrorvalue.

#####

#CIRC!

#DIV/0!

#N/A

#NAME?

#NULL!

#NUM!

#REF!

#VALUE!

Showorhidethespreadsheettoolbar1. Right-clickacellinthespreadsheet,clickCommandsand

Optionsontheshortcutmenu,andthenclicktheWorkbooktab.

2. UnderShow/Hide,selectorcleartheToolbarcheckbox.

microsoft office spreadsheet component help€¦ · when you view and interact with a spreadsheet...

Documents