Spreadsheet Acct Exam 2
A name that represents a cell, range of cells, formula, or constant value. You can create your own defined name, and Microsoft Office Excel sometimes creates a defined name for you, such as when you set a print area.
Defined name
You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box in what?
Defined range names Table names Macro names
F2
EDIT
=SUMMARY!B1
If the linking formula uses a cell on a different sheet, it is called a 3-D reference formula
Be able to enter the APPLICATION.screenupdating = true or false macro code in Excel
Top line
DEFINED RANGE NAMES and TABLE NAMES. True or false:The first character of a name must be a letter or an underscore character or the backslash character (\) \Names are listed first, _Names second
True
True or false: Paste by itself is the same as using Paste Special, Paste: Html Format (this is the Default paste choice in Microsoft Office) CTRL + V
True
True or false: There can be 32,767 characters stored in one cell and all will display in the formula bar, but only 1,024 will display in the cell
True
True or false:A change to either the PivotTable or the PivotChart will change the other one too--both are just a report about data and one report is in a table format and the other report is in a chart/picture format. They are the SAME THING.
True
True or false:A formula can contain 8192 characters; a conditional if can have 127 criteria, 127 criteria ranges, and 1 sum/average range.
True
True or false:ALL PASTE LINK CHOICES are a picture of something stored in another file and will update for changes made to the source document, except for the Word Hyperlink choice, which is only a hyperlink, not a data link.
True
True or false:Basically the only thing you can't change are the formula in the Values field and the numbers in the table. The Pivot Table is "linked" to the database but it isn't a "real time" link; the Pivot Table/Chart must be Refreshed to update for the changes made to the database or to get back the original labels used in the database if they are changed in the PivotTable.
True
True or false:Bitmap (the information in the picture cannot be edited (although the picture itself can be modified with borders and shadows, etc.)
True
In data processing, a ______ is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet, displaying the results in a second table showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations. The user sets up and changes the summary's structure by dragging and dropping fields graphically. This "rotation" or pivoting of the summary table gives the concept its name.
pivot table
True or false: Know what names will be listed first and know what Excel does with special characters in labels you use as range names when using the Create from Selection name command (practice it) • Names must start with a backslash, or underscore, or a letter • All illegal characters (such as a space or other special characters on the keyboard) contained in a label used for a name will be replaced with an underscore, • Backslash defined names are listed first, then underscore defined names, and then alphabetical names • Know the advantages of using names over cell references [saves time, better understanding of formulas, and makes a formula more accurate]
tRUE
All the other paste link choices let you make a ___________ change to the pasted object you saved in Word (formatted, unformatted, html). The HTML choice is the default worksheet paste operation in Microsoft Office and it always allows you to modify the object (temporarily when the object is linked; permanently when it is just pasted).
temporary
How to delete a record from a table that will not delete a row from the worksheet?
use the object menu in the table to pick Delete and choose Table Rows.
What is the advantage of putting them in the Personal workbook?
• It loads automatically when Excel is loaded so macros in that file are always available on that computer in any Excel file; • The Personal.xlsx file is loaded when Excel loads but it is a hidden file so unless you go to View, and choose to Unhide it, you will not be able to edit the macros it contains.
Where can macros be stored (there are 3 different places
• Normal to save the macro with the workbook they are built in • Can be saved to a new workbook (perhaps start a new workbook called macros.xlsm) and save all your macros in it and carry that file with you to load when you want to use your macros • Store them in the Personal workbook
Know how to save an Excel file to store the modules with the macros you recorded
• Save As Excel Macro Enabled Workbook (XLSM extension)
What are the four different ways macros can be run?
• Use Alt F8 (Macro) and the run button • Use the keyboard shortcut keys • Use a macro button (or attach the macro to any object) • Use a macro button on the quick access toolbar
MACRO BUTTONS: What is the advantage of using a macro button on the quick access toolbar over using a macro button on a sheet?
• You don't have to look for the sheet with the macro button and it won't get moved, resized, or deleted with user actions to the sheet (like inserting and deleting rows and columns) • You can run the macro on any sheet in any open workbook as long as the Excel file with the macro is open • Even if a button is placed on the quick access toolbar, the macro assigned to the button won't work if the file with the macro module is not loaded
The name manager can be loaded with
Ctrl F3
A 3-D reference means cells from a different sheet are used in the formula. Cell is always preceded with an _____
!
Create from Selection shortcut
(Ctrl Shift F3)
Use in Formula/Paste Name shortcut
(F3)
e. ALT F11
(like using the Developer tab, Visual Basic)—takes you into the VB editor
How to turn off the filter arrows and the expand/contract arrows?
(show/hide buttons)
The __ stands for all characters in this place
*
Know how to add the Fill Across Worksheets command to the Quick Access Toolbar
1. You can find it in All Commands in Excel Options, Quick Access Toolbar 2. Or you can find it in the Fill button on the Ribbon and right click on it to add it to the Quick Access Toolbar when several sheets are grouped
Functions can contain ____ arguments, but an IF statement can only have 64 nested IF statements
255
DEFINED RANGE NAMES, TABLE NAMES, and FIELD NAMES name can contain up to _____________characters. But a MACRO NAME can only contain _______
255 64
If the linking formula goes to a different file, it is referred to as a _________________
3-D external reference formula
There are ____ functions in Excel
461
With version________ included Visual Basic for Applications (VBA), a programming language based on Visual Basic which adds the ability to automate tasks in Excel and to provide user-defined functions (UDF) for use in worksheets. VBA is a powerful addition to the application. Macro recording can produce VBA code replicating user actions, thus allowing simple automation of regular tasks. UDF are an extension of Excel's built-in function library.
5.0, Excel
There can be ___ sort keys set in a sort.
64
The ___ stands for one character in this place
?
=A2 is what type of formula
A simple linking formula links to a cell on the same sheet
F4
ABSOLUTE
When you point to build external linking formulas, the cell(s) you select will be _______ by default but you can use the F4 key to remove the $ so the formula can be copied down/across
Absolute
DEFINED RANGE NAMES, TABLE NAMES, MACRO NAMES: Names can contain uppercase and lowercase letters, periods and underscores but no other special characters. But _____character can be used within a FIELD NAME
Any
Know how to create a picture of the PivotTable
Create a pivot chart
_____ allows you to select individual nonadjacent sheets to group them
Ctrl
• Introduced the ribbon—the results-oriented user interface • Can customize the quick access toolbar • Themes were added • Conditional formatting—data bars, color and icon sets • Grew to 1048576 rows and 16384 columns • Function autocomplete was introduced (getting drop down lists of function names and defined names when building a function) • Introduced tables—total rows--table styles • Page layout view was added
Excel 2007
• Backstage view was added • Sparklines to summarize trends next to the data • Slicers for pivot tables • Paste with live preview • Table headers will go in the worksheet headers as you scroll down • Instant access to chart formatting options if double click on a chart element • File sizes are half as large as previous excel files (xlsx files versus xls files) • Easy to see symbols in the filter buttons showing if the field was filtered or sorted
Excel 2010
• Flash fill • Recommended charts • Filter table data with slicers • One workbook, one window • The Format Chart pane • The three new chart "charm" buttons • Filter charts—animation used when chart data is updated
Excel 2013
Use Paste Special, Paste, _____________ to EMBED a worksheet object and know what embedding means (putting an entire worksheet file in Word that can be edited with Excel's menu commands). The data is NOT linked to the source it came from; but when the object is pasted into Word, it also creates a new workbook that is stored in Word and will load from the object in Word when you right click on the object and use Worksheet Object, Open [this is new in Excel 2013]. Excel is embedded in Word with Excel's ribbon at the top of the small worksheet.
Excel Worksheet Object
Debugging (use the Debug menu to Step Into, Step Out, Step Over) or just use the ____ key to Step through and execute each line of code after you start the debugger
F8
EXTERNAL REFERENCE FORMULAS INCLUDE THE _________ IN [ ] AND THE ______ IN ' '
Filename Path
F5
GOTO
F1
HELP
Paste Special, Paste, HTML format (the object can be modified with Word) and ______ linked to the source. It is just data copied in from another program to save you the time of typing and formatting the same information. Any changes to the object are permanent and will be made with Word and Word's menu.
Is not
FIELD NAMES and MACRO NAMES:The first character of a name must be a ________
Letter
All the items below are facts about pasting a _______ Excel object into a Word document: • A change in the destination file will NEVER change the source • A change in the source will be reflected in the destination file with one exception—Paste as a hyperlink—since this link is not a DATA LINK. • If both files are open (the destination and the source) and you make a change in the source it may not display in the destination file right away. To get the change, right click on the object and choose "Update link".
Linked
ALT F8
MACRO MENU
If instead you choose the _______________ choice to embed, it works just like embedding a worksheet in Excel and the whole Excel file is stored in Word. You either edit it in the little object window with Excel's Ribbon replacing Words, or in a worksheet stored in Word with Excel's menu available in the worksheet. (Read the notes above on working with the Excel Worksheet Object—it is exactly the same).
MS Excel Chart Object
•Use Paste Special, Paste, _________________ (the entire workbook is embedded in Word but the only Excel menu item that comes in are the Chart Tools--not Excel's entire menu). CTRL + V creates a graphic object. •But it also creates an object that is "linked" without telling you it is linked. If you choose Edit, you modify it in Word with Chart tools. If you choose Edit in Excel, the source file is loaded and editing is done in Excel. You can save the changes you make to the original source file or not; either way the changes you made will be reflected in the graphic object inserted in Word.
MS Excel Graphic Object
Enable the macro by replying to the security message in the _____________
Message Bar
F8
Move through line of macro code when debugging a macro When in visual basic F8 will start the debugger
F3
PASTE NAME/ PASTE LIST/START WITH = AND USE F3 TO PICK NAME LIST
•Also use _________ to paste a list of defined names which will include some other defined names assigned by Excel when you use certain commands, like Print_Area, Print_Titles. Table names are listed in the names box at the bottom, after defined names, but they will not be listed in a table of Names you put on a worksheet.
Paste (F3)
F7
SPELL CHECK
____allows you to select adjacent sheets
Shift
DEFINED RANGE NAMES, TABLE NAMES, MACRO NAMES.___________ are not allowed. Underscore characters and periods may be used as word separators. But they are allowed in FIELD NAMES
Spaces
A name for an Excel table, which is a collection of data about a particular subject that is stored in records (rows) and fields (columns). Excel creates a default Excel table name of Table1, Table2, and so on, each time that you insert an Excel table, but you can change a table's name to make it more meaningful.
Table name
True or false:Early in 1994 Microsoft Excel 5 brought a new functionality called a "PivotTable" to market. Microsoft further improved this feature in later versions of Excel. Excel 97 included a new and improved PivotTable Wizard, the ability to create calculated fields. Excel 2000 introduced "Pivot Charts" to represent pivot-table data graphically. The term pivot table is a generic phrase used by multiple vendors. In the United States, Microsoft Corporation has trademarked the specific compound word form, PivotTable.
True
True or false:Know that creating a table adds a Table Name to structured data and turns the Filter arrows on Excel will extend the table name to include new records and fields as they are entered when using the Tab key.
True
True or false:The Edit Links button in the Data tab, Connections group, will be active if there are formula links to a different file
True
True or false:The Name Manager can be used to create a name but that's not it's real purpose—there are other faster ways to make names; it's purpose is to MANAGE names—edit them, delete them, and filter them.
True
True or false:When the file is open, the drive/folders and apostrophes around the filename aren't shown: [TA.xlsx]Documentation!$C$3.... Otherwise, the apostrophes are displayed around the ENTIRE PATH in the formula in Excel =SUM('P:\218 In Class\T6\[TA.xlsx]Documentation'!B15:B17)
True
True or false:You can search for words of c?ll to find all 4 letter words starting with c and having one more letter, with LL after that but you cannot search for 1?9 to find numbers that start with one, have one more number, and end with a 9 (like 119 through 199)
True
iAfter starting the macro recorder, the first thing you need to do is press the ___________ if you want the first line of code to be relative (not work in the same cells or the same sheet every time it runs)
Use Relative References button
F11
Uses the selected data to build a chart on a chart sheet- uses the default chart type columns
ALT F11
VISAL BASIC MENU OPENS
F6
WINDOWS KEY (Move between split window)
Picture (Metafile) (this one can be modified if you right click on it and choose to edit it—it then is changed into a ______________ which allows editing content changes). Any change to the object is a permanent change and is made with Word.
Word drawing object
When a sheet used in a 3-D formula has a space in the name, __________ are put around the sheet name
apostrophes
Use the Paste Special, Paste Link, Excel Worksheet Object to get a data link that ________ be modified in Word even temporarily. It is purely a picture link to load the Excel file. Changes to the source are the only way to change the destination object. This one is just like linking with a picture choice like BMP.
can not
DEFINED RANGE NAMES. TABLE NAMES, FIELD NAMES, MACRO NAMES. Names cannot be the same as a ________________, such as Z$100 or R1C1
cell reference
A table must first be _____________ in order to use the Subtotal command.
converted to a range
A 3-D reference means cells from a _______ are used in the formula
different sheet
If you instead _________________________ it will put the object into the Edit mode (just like double-clicking does when you are in Excel). Now this object will display all the sheet tabs right within the little object window and Excel's ribbon will replace Word's ribbon so that the object can be edited with Excel's commands. This is the part of OLE that does not work well in Microsoft Office—in other words, it can cause the computer to malfunction. [This was how editing changes to an embedded Excel object were made in previous versions of Excel]. It is safer to make changes using the new worksheet that is stored in Word, and remember you get this new worksheet when you right click on the object and choose to Open the object. In either case, the embedded worksheet object cannot be modified with Word—it will be modified using Excel's menu.
double-click on the object, or right click and choose Edit,
Know that you have choices for controlling how your computer works with links in 3 places...
in Advanced Excel Options, under "When calculating this workbook" and under "General"; and in the Excel Options Trust Center under "Trust Center Settings for External Content".
The wildcard characters both work properly in Excel criteria for________ but not for number fields
label searches
Create from Selection doesn't include the __________ used for the names—it names the selected cells to the right, left, up or down with the label that is in the selection. The command is usually used to create many names at the same time.
labels
d. ALT F8
like using the Developer tab, Macros or View tab, Macros)—takes you into the Macro menu/dialog box
For embedded objects...Changing the source file (Excel file) won't change this object it isn't a data _____ file
linked
Know how to paste a name in a formula or function, start with = or perhaps sum if the name includes more than one cell, and
• select a cell(s) with a name and the name will be automatically pasted in the formula, • or use Paste (F3) to pick a name, • or use the Ribbon command Use in Formula to pick a name from that list; or choose Paste Names at the bottom and pick a name, [F3 us the keyboard shortcut for Use in Formula/Paste] • or type the first letter of the name and choose the name from the list—AutoComplete • All of these ways make a formula and use names instead of cell addresses.