Excel 2010: P4 Visual Presentation

Chapter 38: Saving Time Using the Easy-XL Program

Downloading and Installing Easy XL Install: Easy-xl.com/excel2010indepth/ A. Easy XL Works Best with Tabular Data Field headings in row 1 with no blank columns and rows Select Easy XL, Other, Open Easy XL Sample Workbook Doing Away with VLOOKUP Solve problems with VLOOKUP in Easy-XL To merge data from two worksheets: In a worksheet, select Easy XL, Merge Sheets, Select a Sheet to Merge with the worksheet dialog Select a column and click Select. Excel displays the Merge Sheets dialog In the Merge Sheets dialog, select the worksheets You will have a brand new worksheet A comment in cell A1 indicates how this sheet was created A audit trail of which worksheets were merged and how You will never get that level of detail with the VLOOKUP method The tables are merged to the new data The headings indicate that the data came from a worksheet The data is now values instead of formulas A. Using a Fuzzy Match When you look at your data, look for any misspellings The Easy-XL Merge dialog box offers Both sheets have a customer name and then a sales amount To perform a fuzzy match: Start on a worksheet Select Easy-XL, Merge Sheets, Merge Sheets dialog, select the Levenshtein Distance Fuzzy Matching Text to Columns on Steroids Text-to-columns feature: Limit the maximum number of characters of the column names when you are splitting the column names In the delimiter drop-down, you have strange delimiter (you can break the data), usual delimiter (plus all 255 ASC II characters) Mark the Split from Right-hand Side checkbox when you want to a specified letter from the column name To solve this problem: Select Easy-XL, Columns, Split Columns, select Name, Split Column into Words Sorting Columns Left to Right Rearranging columns after merging sheets: Select Easy-XL, Columns, Rearrange Columns. Easy-XL displays the Arrange Columns dialog, rename, format Summarizing Data You want to see the total of object by condition: Select Easy-XL, Summarize/ Pivot The Easy-XL inserts a new worksheet for the summary A. Adding Statistics to the Report In the Summarize/ Pivot dialog, select the Fields in order to add summary of a statistic report. B. Getting Quick Statistics Select any range of cells Right click and select Easy-XL Quick Stats, a flyout menu Transforming Data Instead of TRIM(), PROPER(), CLEAN() Select the cells and use the Easy-XL flyout menu A. Adding Text to Cells Easy-XL, Cells, Fill Selected Cells, Fill with Text, Append to Cells, Fill Cells dialog To insert values with the .tif and paste with values B. Filling in the Annoying Outline View When you have created a Pivot Table, you would like to remove the blank columns and rows Use Easy-XL, Cells, Fill Selected Cells, Fill Blank Cells with Last Non-Blank Cells to fill the outline view in the Fill Cells dialog There's More To split the data in two worksheets: Use the Easy-XL, Split Sheet, by Group, and Easy-XL Use the Global Find and Easy-XL to go through all the records from the data in the folder Use Import, File and Folder Listings to bring a list of fields in a folder into Excel. Then you can format Select to Index, then you will have an index worksheet with hyperlinks to all of the worksheets in the workbook, plus a list of all cell comments A. Deal with Fiscal Years You can query with the function and the new function added to the Easy-XL by the Query Sheet, Query by Formula dialog B. Record Easy-XL Commands into VBA Macros MrExcel.com has an active community where you can have a discussion forum mostly containing VBA Macros. The Easy-XL will detect if the macro reader is on and this will perform a VBA code as you perform the actions.

Chapter 36: Printing

Printing from Backstage View A. Choosing a Printer Status of a printer B. Choosing What to Print Print Area- only the ranged that you specified will be printed Group Mode-all of the selected print sheets Entire Workbook-all the nonhidden worksheets in the workbook will print. The option to have the pages numbered. Selection- to override the print area temporarily for a small range of a large report. Ignore Ignore Print Area-to ignore any print areas specified previously. Entire used area of the worksheet to be printed Pages spin button-Enter the same number in Pages and To boxes for printing one page number C. Changing Printer Properties Printer Properties to support vendor supplied Collated and staple options D. Changing Some of the Page Setup Settings Payout Layout tab, Backstage View Page Setup dialog of the print preview Page Scaling or Rows to Repeat at Top Here E. Using Print Preview Controls After the Zoom and Show Margin checkboxes, you can resize the margins and the column handles in the Print Preview. F. Closing Backstage View Click any ribbon G. Printing Using Quick Print Quick Access Toolbar, Quick Print icon for automatically printing without the preview Using Page Layout View View tab, Page Layout Add header and footer Change margin by dragging the columns Print Titles are different from Freeze Panes Click to Add Data when you are in the Page Break Preview Hide White Space for the white space in the Page Layout Using the Improved Headers and Footers To add a header in the page layout view, click Design tab, Header & Footer Tools Select View, Workbook Views, Page Layout A. Adding an Automatic Header File path and file name options B. Adding a Custom Header Type the header in the section, then type the text for the header, press Enter C. Inserting a Picture in a Header Add a picture either a small picture or a large picture that extends below the header area and acts as a watermark behind the worksheet. To add a picture:; Select View, Page Layout View Click the header Header and Footer Tools Design tab, select Header and Footer Elements, Picture, Insert Picture dialog, Format Picture dialog, Change the Color drop down to Washout, does not show for the Live Preview printing D. Using Different Headers and Footers in the Same Document The same header/footer in all pages One header/footer for one page, and the same for the rest of the pages Odd and even pages One for the first page, odd and even pages Header and Footer Tools Design tab, Options group for the Different First Page E. Scaling Headers and Footers To have 100% scale of the page number regardless of the zoom for the sheet Header and Footer Tools Design tab, select Options, and clear the Scale with Document checkbox Page Layout tab, Page Setup, Background icon is only for the background on the displayed page Using the Page Setup and Sheet Options Page Layout tab, Page Setup, Scale to Fit, Sheet Option groups, A. Adjusting Worksheet Margins Best Method: Page Setup dialog B. Adjusting Worksheet Orientation Landscape or Portrait C. Setting Worksheet Paper Size Page Layout tab, Size drop-down D. Setting the Print Area Page Layout tab, select Page Setup, Print Area, Set Print Area E. Adding Print Titles Page Layout tab, Page Layout dialog, Print Titles icon Rows to Repeat Columns to Repeat F. Scaling Options Page Layout tab, Scale to Fit group Scale to Fit 1 Page Wide G. Printing Gridlines and Headings Page Layout tab, select Sheet Options, Gridlines/ Headlines (column or row headings), Print Working with Page Breaks View tab, Page Break Preview mode Automatic depends on the column width/row height, and add/delete rows Manual A. Manually Adding Page Breaks To add a page break, manual select the entire row or column B. Manual Versus Automatic Page Breaks In the Page Break Preview, you see solid lines for the manual and dashed lines for the automatic page breaks. C. Using Page Break Preview to Make Changes In the Page Break Preview, you can drag the line D. Removing Manual Page Breaks Page Layout tab, select Page Setup, Breaks, Remove Page Break To remove a manual page break for a row, select the row below the page break To remove a manual page break for a column, select the column to the right of the page break

Chapter 37: Excel Web App and Other Ways to Share Workbooks

Sharing Workbooks with Others A. Using the Excel Web Application Multiple people can edit the same worksheet at the same time SkyDrive Windows Live ID, Office Web Apps, Windows Live You have features: slicers, pivot table with row labels repeated, some GetPivotTable formulas, chart B. Publishing a Workbook to the Excel Web App To publish the workbook: Excel Web App, use File, Save & Send, Save to Web, SkyDrive folders, click Save As From the top navigation bar, select More, SkyDrive Select view, then you will have a web page of the spreadsheet in the browser When you are in the View mode, you can change the pivot table and the chart by using the slicers C. The Amazing Edit Button Edit in Browser Edit mode, edit the cells, enter the formulas, copy and paste Home tab, Excel Web App: Format the font, alignment, wrap text, and number format and all the features in the home tab. File menu, Save As to the SkyDrive, you can download a copy of the workbook or a snapshot of the workbook to your computer. D. Advantages of Creating a Client Version of Your Workbook Create the workbook then upload it in the web with limited functionality. However to note: The columns remain hidden in the web after the Excel The slicers, pivot tables, or charts will render in the web The GetPivotData functions that you created in Excel work in the web Some of the features will not work: Data Validation Links to external web Worksheet protection Sending a Workbook via Email Open the workbook, arrange the window for the recipient, From the File menu, select Save and Send, Send Using Email, Send as Attachment Outlook Send Mail window A. Creating a PDF from a Worksheet Create PDF/XPS document Publishing to Excel Services on SharePoint Excel Services open the spreadsheet Refreshes an external data Builds a DHTML view of the spreadsheet in the browser Sharing spreadsheets through a browser Populating key indicator section of execute dashboards Reusing logic encapsulated in Excel spreadsheet as a back end to any language that can speak web services A. Defining Interactively in Excel Services You can only use the SharePoint Scrolling Access autofilter drop-downs or slicers to filter the data Access age field drop-down in pivot tables Change certain cells that the author of the spreadsheets defined as workbook parameters The author must define parameters with named cells. B. Saving Workbook to Excel Services To save a file to Excel Services: Identify the cells that you want to be changeable parameter in the browser For each Parameter cell, select Formulas, Define Name and assign a name: naming a cell Save the file to the SharePoint In the right pane of Backstage view, select Publish Options for the dialog Show tab-choose which worksheets should be published Parameters tab-select Add Choose each named range/ slicer Click Save As to a SharePoint Library Excel provides feature support when it is published in the Add Parameters dialog. You can name the cells, range and view the workbook with the drop down menus Interacting with Other Office Applications You can add portions of information into the OneNote notebook Access a list or section of information with Mail Merge Data from Access can be also used as the source data for an external query. You can have a body paragraph from Word to Excel. A. Pasting Excel Data to Microsoft OneNote The capacity to organize all notes in one place Render worksheets in a tabular grid with the paste option Keep all icon sets, charts and so on. B. Using Excel Charts in PowerPoint Copying charts from Excel to PowerPoint The resulting chart is completely editable in PowerPoint Changes to the slide theme change the look and feel of the chart To copy a chart to PowerPoint: Prepare a blank slide in PowerPoint Open the workbook Select the chart to be copied Press Ctrl + C to copy the selected chart Switch back to PowerPoint Home tab, select Paste C. Creating Tables in Excel Text Box After calculating the formulas, paste with values. An HTML presentation of the table is pasted onto Word. D. Pasting Word Data to an Excel Text Box Type paragraphs without thinking about the length of the line and use the Text Box dialog. E. Using Excel Data in a Word Mail Merge To do not include blank rows nor columns Select the range to convert it a table Select Mailings, Start Mail Merge, Step by Step Mail Merge Wizard, task pane Mailings tab, select Insert Merge Field Print-A letter per row in the excel Edit Individual Letters-creates a new document with a new page for each document F. Building a Pivot Table from Access Queries Access is a relational database To use the data from Access in an Excel pivot table: The file must be in a trusted location: Excel, Select File, Options, Trust Center, Trust Center Settings, Trusted Location. If not, select Add New Location Select Insert, PivotTable for its dialog Then Existing Connections dialog Select Table dialog Queries in Access are shown as views Two relatively small Access tables are joined into a larger query results. The pivot table presents results from the query without having to store the detailed data in Excel.

Chapter 32: Using Excel Charts

Understanding the Components of a Chart Source Data The components of a source data: Series name, series values(x and y axis), category labels, if your headers contains numbers or dates then ensure the upper left corner is blank. Setting Up Data for Charting Ensure you have all the components of the source data, this includes having a continuous range of data, you have the headings (x and y axis). Inserting a Chart by Choosing a Chart Type The Insert tab, Charting group, Column, Line, Pie, Bar, Area, Scatter, Other Charts Select the data including the headers and press the desired chart icon A. Using the Create Chart Dialog All Chart Types or select Design, Change Chart type. Change Chart Type dialog Clustered-the values are plotted side by side. This is not ideal for the total sum of a value Stacked-the values are plotted on top of each other. This is difficult to compare the total sum against the values for the trend. 100% Stacked-the values are plotted on top of each other except the values lead to 100 percentage. Click in the More Options in the drop down menu after inserting a chart or click the icon on the ribbon. B. Changing a Chart's Type On the Design tab, the leftmost icon is the Change Chart Type icon to change the logical groups. C. Moving or Resizing a Chart To move a chart, hover the mouse above the chart and wait for a ToolTip to appear Chart Area. To resize a chart, click the dots in border of the chart and drag. Choosing a Chart Layout to Further Customize the Chart Type 12 styles for a line chart 11 chart styles available for 2D clustered column charts Gerry Verschuuren's book, Excel for Scientists and Engineers Create a new layout with the template folder. Customizing a Chart Using the Chart Tools Tabs Design-for major changes to the entire chart Layout-for adjustments to the 11 major elements of a chart Format-further refinements A. Customizing a Chart by Using the Design Tab Customize the colors in the chart: Chart Styles gallery from the Design tab. The theme will depend on what you have choose on the Page Layout tab, Themes group, and Charts icon. You can further customize the color in your chart with the Layout and Format after customizing a theme from a style, which will allow you to match the font and effects for the desired element. B. Changing Chart Settings Using the Layout Tab In the Design tab, you selected a built-in chart layout to create certain combination of titles, legend, data labels, data table, axed, gridlines, and background. The Layout tab offers drop downs for 11 major elements of a chart. Chart Title, Legend, and Data Labels. The Chart Title drop down as well as icon for the other drop downs Each drop down offers a few choices in the More option. C. Micromanaging Using the Format Tab With Format dialog, you can change the elements: Click the item in the chart, Select the element from the drop-down on the left side of the Layout and Format tabs, Right-click the chart and use the new drop-down in the Mini Toolbar or utilize the Format dialog. After an item is selected, you can format it using any of these methods: • Right click and select the Format from the context menu • Press Ctrl + 1 • On the Layout or Format tab, press the icon Charting Tips and Tricks A. Showing Numbers of Different Scale on a Chart Two scales corresponds to each axis. To enable the second axis due inaccurate representation of a scale chart: Right click the chart, from the Mini Toolbar that appears, open the drop down and select the second series Press Ctrl + 1 for the Format Selection dialog In the Series Options category, select Secondary Axis in the Plot Series On frame. Select Design, Change Chart Type, and then select a Line chart To format the right axis, select Secondary Veridical Axis from the Current Selection drop down. Choose a text fill in color to match the color of the second series Repeat to change the left axis to match the First Horizontal Axis. B. Creating a Chart with One Keystroke One of my favorite tricks in Excel is the trick of creating a chart by using one keystroke. Select the range of data to be charted To create a chart on a new chart sheet, press the F11 key or press Alt + F1 after you embedded the current chart for a new chart sheet. To change the default chart type: Build a chart of the desired chart type Select the chart From the Design tab, select Change Chart Type. The Change Chart Type dialog appears In the lower-left corner of the Change Chart Type dialog, click the Set As Default Chart button C. Adding New Data to a Chart by Pasting You can paste the data to the new chart by reusing the previous chart Follow the steps to expand the chart by pasting new data on the chart: Make sure the new data is consistent with the old data Select the new data including the heading Press Ctrl + C to copy the new data Select the chart Paste Ctrl + V to paste the new data on the chart. D. Adding New Data to a Chart by Using a Table Select a cell in the source data for the chart Type Ctrl + T to make the range into a table Confirm the location for the table Can alter the chart whenever you edit the table. E. Adding Drop Lines to a Surface Chart To make surface charts easier to read, you can add drop lines at every label from the x-axis Select the chart From the Layout tab in the Analysis group, select Lines, Drop Lines. Excel draws a vertical line from the x-axis to the surface of the chart F. Predicting the Future by Using a Trendline To add a treadline in a chart, a treadline is dots connecting to each other from the Layout tab, Analysis group G. Creating Stock Charts To track historical stock performance follow through the Charts, Other Charts. H. Dealing with Small Pie Slices To view the labels in the pie charts, create a pie by using the 3D Pie type in the Layout tab in the Labels group To change an existing pie chart to a bar of pie chart, follow through with the Design tab, Type group. I. Displaying Three Variables by Using a Bubble Chart Can display the relationship between three variables. The third variable is the representation of the size of the bubble at the intersection. J. Changing the Location of a Chart To change the location of a chart, select the Design tab and the Location group. K. Saving a Favorite Chart Style As a Template After designing a chart, you can save it as a template. To create a template, follow through with the Design tab and Type group after building a chart and customizing it. To create a chart by using your template, follow through: Select the data you would like to chart From the Insert tab, choose any of the Chart drop-downs and then select All Chart Types for the dialog. To make your template in default style: Select a chart based on the desire template From the Design tab, select Change Chart Type then the dialog appears Using Pivot Charts A pivot chart is a chart based on an underlying pivot table. As you sort, filter, and change the field layout of the pivot table, the associate chart updates automatically. You can use the Fieild Buttons drop-down on the Analuyze tab to hide the buttons from the char. To a Pivot Chart Filter pane To create a pivot chart, follow the steps: Select a cell in your data From the Insert tab, select Tables, PivotTable, PivotChart, then dialog

Chapter 30: Formatting Worksheets

Use tables styles - with banded rows, accents for totals, please refer to ch. 19 Use cell styles-You can use cell styles to identify titles, headings, and accent cells Use formatting commands-On the Home tab, Format Cells dialog box Why Format Worksheets? The reader can instantly focus on the totals for each line Headings are aligned with the data Borders break the data into sections Accent colors highlight the subtotals and totals The title is prominent in a larger font, and a headline typeface is used Numeric formatting has removed the extra decimal places and added thousands separators The column widths are adjusted properly A short row adds a visual break between the product lines Headings for each product line are rotated, merged and centered. Using Traditional Formatting • On the Home tab, Formatting icon, Format Cells dialog box: • Press Ctrl + 1 • Ctrl + Shift + F to display the Font tab on the same dialog • Click the dialog launcher icons in the lower-right corner of the Font, Alignment, or Number groups. Each icon opens the dialog, with the focus on a different tab • Right click any cell and select Format Cells • Select Format Cells from the Format dropdown on the Home tab Font, Alignment, and Number groups on the Home tab Icons for wrapping text, vertical alignment, and text rotation on the Formatting toolbar The Format Cells dialog: Number-You can also use the Custom category formatting Alignment-Offers setting for horizontal alignment, vertical alignment, rotation, wrap, merge, and shrinking to fit. Font-Controls font, size, style, underline, color, strikethrough, superscript, and subscript. Border-Controls line style and color for each of the four borders and the diagonals on each cell. Fill-Offers 16 million fill colors and patterns. Cell gradients Protection-used to lock or unlock certain cells A. Changing Numeric Formats by Using the Home Tab Home tab, Numeric group: currency, percentage, comma style, Increase and Decrease Decimal icon The drop-down menu has a dozen popular number styles General Format-Is a number format, decimal places, no thousands separator is used, a negative number is shown with a minus sign before the number Number-does not use a thousands separator, forces the decimal places, even though the numbers may not have them Currency-the symbol is shown before the number, which has the decimal places. A negative number is shown with the hyphen Accounting-negative numbers are shown in parentheses. The symbols are left aligned with the edge of the cell. The positive numbers are aligned in the right side of the cell for aligning up the negative numbers. Percentage-uses two decimal places when selected from the drop-down. The percentage icon is better to use on the ribbon rather than the Format Cell dialog box. Fraction-the drop down rounds the integer to one digit divisors B. Changing Numeric Formats by Using Built-In Formats in the Format Cells Dialog The Format Cells dialog offers far more number formats than the Home tab The Number tab is the active tab when you display the dialog by clicking the dialog launder icon in the lower-right corner of the Number group in the Home tab. The General and Text categories each have a single setting. The Custom category allows you to use formatting codes to build any number format. The remaining nine categories each offer a collection of controls to customize the numeric format. a. Using Numeric Formatting with Thousands Separators Press Ctrl + 1 for the Format Cells dialog Select the Number category from the Number tab Select the Use 1000 Separator check box Optionally, adjust the Decimal Places spin button to 0 Optionally, select a method for displaying negative numbers C. Displaying Currency The drop down offers 409 different currencies from around the world D. Displaying Dates and Times 17 built-in formats for displaying dates, and the Time category offers 9 built in formats for displaying time. The M type displays month names in JFMAMJJASOND style each month is represented by the first letter of the month in this style, which works great when you label along the x-axis in the chart. E. Displaying Fractions Excel can display decimals as fractions in a variety of formats F. Displaying ZIP Codes, Telephone Numbers, and Social Security Numbers Four special formatting in the US cetic: The Zip Code and the zip code plus 4 styles ensures that east coast cities do not lose the leading zeros in their zip codes. This will give you the correct zip code number to include the zero in front of the digits. The traditional phone number format with the parenthesis and the hyphen The Social Security Number groups the number in hyphens. G. Changing Numeric Formats Using Custom Formats Press Ctrl + 1 to display the Format Cell dialog for the custom formatting. a. Using the Four Zones of a Custom Number Format Whether the cell contains a positive number, negative number, a zero or text, a custom number format can contain up to 4 formats separated by a semicolon: • Separated formatting codes for zone by using the semicolon • If you type only one number format, it applies to all numbers • If you type only two formats, the first format applies to positive and zero. The second format is sued for negative numbers. • If all four formats are used, they refer to positive, negative, zero and text values, respectfully. b. Controlling Text and Spacing in a Custom Number Format • You can display a mix of text and numbers in a numeric cell The text is proceed in quotation marks. If you need a single character, then you omit the quotation marks and add a backslash like \M • Special characters do not require a backslash nor quotation marks $, -, +, /, (), :, !, ^, &, ', ~, {,},=,<,> and the space character • To add a specific amount of space to a format, you enter an underscore followed by a character • To fill the space in a cell with a repeating character, use the * followed by a character. For example, the format **0 fills the leading space in a cell with *. The format 0*- fills the trailing space in a cell with hyphens. • If you are expecting numbers but think you might occasionally have text in the cell, you can use the fourth zone of the format. Use the @ character to represent the text in the cell. For example, 0;0;0 "Unexpected entry of "@ highlights the text cells with a note c. Controlling Decimal Places in a Custom Number Format • use the zero for the decimal places 0.000 for a whole integer • use a pound sign # as a placeholder to display significant digits but not insignificant zeros for example 0.### displays up to three decimal places • use a question mark to replace insignificant zeros on either size of the decimal point with enough space to represent a digit in a fixed width font. This format was designed to allow decimal points to line up, but with proportional fonts, it may not always work. • To include a thousands separator, include a comma to the left of the decimal point. For example, #,##0 displays a thousands separator • To scale a number by thousands, include a comma after the numeric portion of the format. Each comma divides the number by a thousand. For example, 0, displays number in thousands, and 0, displays numbers in millions. d. Using Conditions and Color in a Custom Number Format You can consider the flexible conditional formatting features for any new conditions: However, You can include a condition in square brackets after the color but before the numeric formatting [Red][<=100]; [Color 17][>100] The telephone special formatting uses the custom condition [<=9999999] ###-####;(###)-#### e. Using Dates and Times in a Custom Number Format The various m and d codes allow flexibility in expressing dates The date and time formats table is shown in page. 888 f. Displaying Scientific Notation in Custom Number Formats If a format contains a zero (0) or a pound sign(#) to the right of an exponent code, Excel displays the number in scientific format and inserts an E. The number of zeros or pound signs to the ride of a code determines the number of digits in the exponent E - or e- places a minus sign by negative exponents. E+ or e+ places a minus sign by negative exponents and a plus sign by positive exponents: 1450 formatted with 0.00E+00 display as 1.45E+03 1450 formatted with 0.00E-00 display as 1.45E03 0.00145 formatted with either code display 1.45E-03 H. Aligning Cells Left align text and right align values and dates I. Changing Font Size The Increase Font Size (A^) icon increases the font size in the selected cells to the next larger setting. The Decrease Font Size (Av) icon decreases the font size in the selected cells to the next smaller setting The Font Size drop-down offers a complete list of font sizes. J. Changing Font Typeface The Font drop-down shows the font names in the style of each font K. Applying Bold, Italic, and Underline In the drop down menu, you will view: Single to double underline options The underline style underlines the characters in the cell To under the extent of the entire width of a cell: Single Accounting Underline Double Accounting Underline Bottom border of the cell L. Using Borders The Borders drop-down offers 13 choices found in the Home tab and Font group The Border Drawing Tools: Choose a color in the flyout menu Choose a style for the Line Style Or draw a border by selecting the option Hold down ctrl + dragging to draw a grid in a range Ctrl + Shift while dragging to erase the borders Press Esc to escape after completing this a. Drawing a Border Around a Range Click the Outline button and then the Inside button in the Presets section of the dialog You can hold down the Ctrl when using the Erase Border tool M. Coloring Cells The paint bucket drop-down (background fill of a cell) and A drop-down (font color) In the Format Cells dialog, choose two colors or choose one color and white, choose a shading style, choose one of the three variations. N. Adjusting Column Widths and Row Heights Click the border between the column headings Double-click the border between column headings: to fit the widest value in the column Select many columns and drag the border for one column: you can uniformly adjust all the columns Select many columns and double-click one of the borders between column letters: all the columns adjust to fit their widest value Use the ribbon: Select one or more columns and enter the width in characters Apply one column's width to other columns: select the column, press ctrl + c, select the columns to be adjusted, select the Clipboard section of the Home tab and select Paste, Paste Special, Column Widths, OK. Autofit a column to all the data below the title rows: Click the Click the first cell in the data range, then press the End key. Next, hold down the Ctrl and Shift keys while pressing the Down Arrow Key. This selects a contiguous range from the starting cell downward. Now select the Cells section of the Home tab and then select Format, AutoFit Selection. This method is remembered as Alt + O + C + A O. Using Merge and Center Columns are visually grouped into product lines by merged cells. P. Rotating Text Home group, Alignment tab, Format Cells dialog Formatting with Styles Home tab, Cell Styles, Styles Understanding Themes You have a total of 20 themes. Fonts-one for body text and one for titles. Colors-four for text and backgrounds, six accent colors that are used in charts and table accents and two for hyperlinks. Effects-object effects, such as bevel and line style A. Choosing a New Theme On the Page Layout tab. Listed next are the four drop-downs available in the Themes group: Themes, Colors, Fonts, Effects. Tables or charts are in the right of the screen From the Page Layout tab, select the Themes drop-down from the Themes group Look over the themes. The worksheet updates to show the new colors, fonts and effects Apply the theme to the workbook. You can also see the accent colors in the themes. B. Creating a New Theme Select two fonts and six accent colors for your company's color. a. Understanding RGB Color Codes Colors on computer monitors are described as a mix of red, green, and blue. Each color channel is assigned a value from 0 to 255. To discover the codes for your company's color scheme, follow the steps: Open your company's Home page in a browser In the Internet Explorer, select View, Source. In Firefox, select View, Page Source. You now see the underlying HTML code. Find the colors used in the page by searching for a pound sign (#). A web page specifies colors by using a pound sign followed by six characters such as #4F81BD The Hexadecimal is a numbering system that has digits 0 through 9 and A through F , include 0s. The first two digits represent the red then the green and blue digits in two. b. Converting from Hex to Decimal MID function to extract each pair of numbers from the color code HEX2DEC function converts the 2-digit hex number to decimal c. Finding New Colors To find colors that look good together: Colorschemedesigner.com/ To find complementary colors, follow the steps: Start with a hex representation of one of your logo colors Open the web browser for the website In the bottom, just left of center, click the RGB code In the window that pops up, enter the portion of the color code after the pound sign such as FF9108 Click each of the six icons under the color wheel on the left. The six icons represent mono, complement, triad, tetrad, analogic, and accented analogic. In the Triad view, the website shows your original color, three others, and three variations of each. In the bottom navigation, select Color List. The website shows the hex color codes for all the colors shown. d. Specifying a Theme's Color A theme is composed of two text colors, two background colors, six scent colors, and two hyperlinks colors. You can even customize your own theme e. Specifying a Theme's Fonts The Create New Theme Fonts dialog appears in the Page Layout, Theme, Fonts, Create New Theme Fonts f. Reusing Another Theme's Effects Page Layout, select Themes, Effects, and then chose one of the existing themes Circle, arrow, rectangle icons Mrexcel.com/30fig40.jpg g. Saving a Custom Theme To reuse a theme, you must save it in the Page Layout tab select Themes, Themes, Save Current Theme. They are saved in the Document Themes folder C:\Documents and Settings\user name\Application Data\Microsoft\Templates\Document h. Using a Theme on a New Document Themes drop-down on the Page Layout tab, Custom theme i. Sharing a Theme with Others You would need to send them the .thmx file form the theme folder. You can also use the Browse for Themes folder to select the file. This is located in Page Layout, Themes, Browse for Themes. Other Formatting Techniques a. Formatting Individual Characters To format individual characters, follow the steps: Display the Home tab, Select the cell that contains the characters to be formatted, Press F2 key to edit the cell Using the mouse, highlight the characters in the formula bar Although most of the ribbon is grayed our, the options for font size, color, underline bold, italic and font name are available in the Font group of the Home tab. Apply any formatting from this group If the changes are not visible in the formula bar, press Enter to accept the changes in order to preview them. b. Changing the Default Font To change your default workbook, follow the steps: 1. Font section pf the Home tab, select the Font drop-down to inspect the available fonts in their actual styles. Find the name of the font you want to use. 2. From the File menu, select Excel Options. 3. In the dialog, click the Popular category in the left margin 4. In the second section, When Creating New Workbooks, select the Use This Font drop-down. Select the font name you chose in step 1. 5. Click OK to close the Excel Options dialog, restart the Microsoft and you will have a new workbook with the changed default font. c. Wrapping Text in a Cell Readjust the size of the column cell Wrap text the cells Align the text d. Justifying Text in a Range Offers a command that reflows the text in a paragraph to fit a certain number of columns The first column contains text and the remaining columns to the right are blank Select the range From the Home tab, select Editing, Fill, Justify e. Adding Cell Columns You can customize comments with colors, fonts, or even pictures Right click the selection border and select Format Comment. The transparency setting on the Colors and Lines tab allows the underlying spreadsheet to show through the comment. You can choose the Fill Color drop-down and select Fill Effects and insert a picture as the background in the comment. Copying Formats a. Pasting Formats Press Ctrl + V to paste and Ctrl again to open the Paste Options menu, Type R to paste only the formats. The formats from the original selection are coped to the new range. Although the amounts initially changed after pressing Ctrl + C, the original amounts are restored after pressing R. To paste the column widths without the pasting values on the Home tab, click the Paste drop-down and then select Paste Special, Column Widths, OK b. Pasting Conditional Formats Choose "All Merging Conditional Formats" from the Paste Special dialog to merge the existing icon set in the source range with the existing color scale in the target range. c. Using the Format Painter The new ToolTip for the Format Painter icon offers you to copy a format to many different ranges. Select the range, double click the format painter icon, click a new destination range, the format is coped, or you can drag to paint a different size range. Esc to escape d. Copying Formats to a New Worksheet To copy a worksheet within the current workbook: Activate the worksheet to be copied Hold down the Ctrl key, click the worksheet tab and drag it to a new location. A new sheet is crated with a strange name such as Sheet 3 (2). To copy a worksheet to a new workbook, follow these steps: Activate the worksheet to be copied Right click the sheet tab. Select Move or Copy to display the Move or Copy dialog In the To Book drop-down, select (new book). Click Create a Copy. Click OK. The single worksheet is copied to a new workbook. Elbow Formatting: In one cell of the header located in the left top corner you have a diagonal line across to split the heading shown in the top row and the left column. Excel troubleshooting: Unmerging Cells in Data Pasted from the Web To turn off merged cells in data pasted from a web page, follow these steps: Select the pasted data Display the Format Cells dialog by pressing Ctrl + 1 Select the Alignment tab The Merge Cell icon has a square in the check box to indicate that the selection has a mixture of merged and unmerged cells. Click the box once to select the box. Click the box a second time to clear the Merge Cells check box. Click OK. The merged cell are converted to individual cells. You can now sort and copy the data as usual.

Chapter 31: Using Data Visualizations and Conditional Formatting

Using Data Bard to Create In-Cell Bar Charts Data bards can be solid, gradient and negative (shows a different color and/or the right axis center). The values that are zero have no fill in color. In a cell you can have the negative number show to the left of the cell and the positive numbers will be shown to the right of the cell. You can also have the negative numbers show the same as the positive number but with a different color. You can control the axis color, border and the number colors. You can specify the scale for example, the min, max, lowest value, percentile You can either show or hide numbers in the data bar. The numbers are hidden by conditional formatting with a picture linked by pasting it over the words in the cell. A. Creating Data Bars Select the numeric data without the total. From the Home tab, select Conditional Formatting, Data Bars. B. Customizing Data Bars From the Conditional Formatting drop-down on the Home tab, select Manage Rules. From the Show Formatting Rules drop-down , select This Worksheet. You now see a list of all the rules applied to the sheet Click the Data Bar rule Click the Edit Rule button. You see the Edit Formatting Rule dialog A number of customizations are available in this dialog: Select the Show Bar Only setting to hide the numbers in the cells and to show only the data bar. Then customize your data, have fun! C. Showing Data Bars for a Subset of Cells You add a new conditional format (a very boring format) to all the cells that you do not want to have data bars that may have the top 10. Turning on Stop if True(in the Conditional Formatting Rules Manager dialog) is the key to getting Excel to not apply the data bar to cells with values outside of the top 10 (managing the rules). Using Color Scales to Highlight Extremes Select the range without the headers and the total number, select the Conditional Formatting, Color Scales from the Home tab. From the Color Scales flyout menu, select one of the twelve styles. A. Customizing Color Scales You are not limited to 2 scale coloring, you can have 3 scale coloring. You can do this by the Home, Conditional Formatting, Manage Rules, and Edit Rule. In the dialog, you set the values where to assign the max, min, ect. You can also have the Mix Conditional Formatting (apply for the color scale and icon set). If you have 2 color scales, then will have 2 different rules applied to the cells as shown in the Manage Rules dialog. Using Icon Sets to Segregate Data Based on the icon set, Excel automatically applies an icon to a cell based on the relative size of the value in the cell compared to other values in the range. The icon is set to show positive, neutral or negative meanings. Three icon sets-you have a choice between arrows, flags, two varieties of traffic lights, signs, stars, triangles, and two varieties of what Excel calls "3 Symbols" (check mark, exclamation point, and an X). Four-icon sets-two varieties of arrows: a black-to-red circle set, a set of cell phones power bars, and a set of four traffic lights. Five-icon sets-there are two varieties of arrows, boxes, a five-power bar set and an interesting set called "5 Quarters" A. Setting Up an Icon Set Are they going to be printed in monochrome or displayed in color? To set up an icon set: Select the numeric data in the range without the Grand Total, Totals, and Headers. From the Home tab, select Conditional Formatting, Icon Sets. Select 1 of the 20 icon sets B. Moving Numbers Closer to Icons Icons are left aligned Numbers are right aligned or center aligned Select Right (Indent) for the icon to be centered align Bump the indent figure up to move the numbers closer to the icon. Changing the alignment of the numbers move them closer to the icon The indent can also be set to four characters Edit the Conditional Formatting Rule if you do not want to show the numbers. Show Icon Only. You can align the icon. Show Icon Only and then pasting a linked picture from other cells showing the numeric values. Select one of the cells with the icon set formatting From the Home tab, select Conditional Formatting, Manage Rules. In the dialog, click the Icon Set rule and then click the Edit Rule In the middle of the Edit Formatting Rule dialog, select Show Icon Only. Select the cells that contain icons and click the Align Center button on the Home tab Set up a formula to point to the number in the top left corner of the icon set range. Copy the formula and adjust the cell as your icon set range. This will give you a range of just the numbers. Format this range of numbers to be right-aligned with an indent of 1. Copy the range of numbers Go back to the original set of icons and Paste, Picture Link. A picture of the original numbers will appear, behind the icons. Using the Top/ Bottom Rules Specify a particular number limit and any of the conditions listed in the following: Top 10 Items Top 10% Bottom 10 Items Bottom 10% Above Average Below Average A. Setting Up Conditional Formatting Rules From the Home tab, select Conditional Formatting, Top/Bottom Rules, and then choose one of the six rule types. The dialog for above/ below average does not require you to select. The N is the threshold value. The spin button updates the selection with the appropriate number of highlighted cells. You can customize your format, then format the cells based on the selection. By default, rules added most recently are applied first. Using the Highlighted Cells Rules Conditional Formatting drop-down menu, Highlight Cells Rules, More Rules option: Text That Contains-lets you highlight text string A Date Occurring-conceptual dates Duplicate Values-highlight both records of a duplicate or highlight all the records that are not duplicated. A. Highlighting Cells by Using Greater Than and Similar Rules To base the conditional formatting threshold on cells without having to resort to the formula option of the conditional formatting. To set up a rule to highlight values greater than a threshold, follow the steps: Select a range of data. Unlike with the other rules, you might choose to include totals in this selection. Select Home, Styles, Conditional Formatting, Highlight Cell Rules, Greater Than to display the Greater Than dialog box. To have complete control over the number format, font, borders and fill, customize the format. OK. The formula is compared to the active cell in the selected range behind the dialog box. The threshold starts with the cell being referenced in the dialog box compared to the dialog. Set up the rule From the Conditional Formatting icon, select Manage Rules Select the Less Than rule and click Edit Rule Use the drop-down shown to select Less Than B. Comparing Dates by Using Conditional Formatting If you define a feature to highlight dates from last week, the rule automatically update based on the system clock. Based on the conditional formatting, new dates are formatted whenever you open the workbook. C. Identifying Duplicate or Unique Values by Using Conditional Formatting Marking duplicates or unique values with the built-in conditional formatting choices requires additional work to decide which of the duplicates to keep in order to produce a unique list. In Edit Formatting Rule dialog, enter this formula: =COUNTIF(E$1:E1,E2)=0 to highlight the unique values. Then you would have a list of values that are not highlighted and are of duplicated values. This is a better method to review for any data discrepancies. The error may have been a client ID duplicate instead of the service provided. D. Using Conditional Formatting for Text Containing a Value To search text that contains a certain value or values: Select the cells Home tab, Conditional Formatting, Highlight Cells Rules, Text that Contains To mark cells based on a partial value In the Refers to box, enter a comma, a space, and the state that you want to find. Note, this is not case sensitive, meaning pa can be PA. Then, choose an appropriate color from the drop-down menu In the Find dialog box, you are allowed to use wildcard characters: * to indicate any number of characters ? question mark to indicate a single character Tweaking Rules with Advanced Formatting A quick formatting icon More Rules option New Formatting Rule Dialog The formula option is harder to use than the quick formatting icons because the user utilize the options for specific rule types. The six rule types are in the top of the New Formatting Rule dialog. The items are listed in the right column are advanced options that are available only by clicking More Rules. Options for Formatting Cells Based on Content 943 and 944 Options for Formatting Values that are Above or Below Average A. Using a Formula for Rules You can build a logical formula to describe the condition you can build your own conditional formatting rule based on a formula. You can build a rule by first understanding all of the rules This allows you to compare cells on one worksheet to a worksheet from a previous month or to use a VLOOKUP table on another worksheet. a. Getting to the Formula Box To set up a conditional format based on a rule: Select a range of cells In the Style group of the Home tab, select Conditional Formatting. Add New Rule. In the New Formatting Rule dialog, choose the rule type Use a Formula to Determine Which Cells to Format. You now see the New Formatting Rule dialog box. b. Working with the Formula Box The formula must start with an equal sign The formula must evaluate to a logical value of TRUE or FALSE. The numeric equivalents of 1 and 0 are also acceptable results Absolute reference in conditional formatting Write a formula that will appear in the active cell when you write a relative formula If you need to select the cells you can collapse or expand the dialog Three possible status is indicated in the lower left corner of Excel: Enter(enter your formula), Point(the selected cell's addresses is added to the formula box), and Edit (in the Edit mode) B. Finding Cells Within Three Days of Today If the active cell is B2, then the formula is: =ABS(TODAY()-B2)<4 C. Finding Cells Containing Data from the Past 30 Days It would be more predictable to write a rule that shows the past 30 days You compare the date in the cell by using TODAY() to make sure the date in the cell is less than today. Because the active cell in Figure31.18 is F4, you use the following formula: =AND(F4<TODAY(),(TODAY90-F4)<=30) you change the 30 to a number of days that you would like to generate. D. Highlighting Data from Specific Days of the Week The WEEKDAY() function converts a date to a number from 1 through 7. When used without any additional arguments, the value of WEEKDAY (date) for a Sunday is 0 and a Saturday is 7. Instead of the date, the argument can be cell addressee. E. Highlighting an Entire Row To highlight an entire row for a condition: Select the entire range Write a rule for all those cells that will look at column D for the same row as the cell Highlight the entire row based on one column, you use the mixed references with a dollar sign before the column letter. To find the largest value in a column, you use the absolute reference to the column =MAX(range with absolute reference). The conditional formatting formula for this specific case is =D2=MAX(rnage with ar) To change this rule to highlight the smallest value in a column , you change max to min To base the test on another column, change the column to another in three places in the formula The combination of a mixed reference and the absolute reference allows you to highlight an entire row. F. Highlighting Every Other Row Without Using a Table The trick to formatting every other row is to check the remainder of the row number after dividing by 2. Excel has function that make this easy. =ROW() returns the row number of the given cell =MOD(ROW(),2) divides the row number by 2 and tells you the remainder. The task is then simply to highlight the rows where the remainder is equal to 1 or equal to zero. If the active cell is A2, the formula is =MOD(ROW(),2)=0 Combining Rules The ability to have multiple conditions evaluate to TRUE You can stop or evaluate rules if you like One rule might set the font color to blue Cells meeting neither rule will be in normal font style Numbers are corresponding to the color font. 10 formatting styles are in table 31.3 Clearing Conditional Formats You can highlight the entire range with conditional formatting and then use Home, Styles, Conditional Formatting, Clear, Selected Cells. This removes all the conditions from the current selection. To clear all the fonditional formats from the current worksheet, you use the Home, Styles, Conditional Formatting, Clear, Entire Sheet. This is handy if yo have only one set of rules set up on the sheet. You can delete all the rules without having to select the entire range. If you have rules assigned to a pivot table or a table, you can select one cell in the pivot table or table. This enables new options for Home, Styles, Conditional Formatting, Clear, This Table or Home, Styles, Conditional Formatting, Clear, This PivotTable To delete just a portion of those rules, you have multiple rules assigned to a range, Home, Styles, Conditional Formatting, Manage Rules. In the dialog, use the drop down menu to display the rules in the current selection, this worksheet, or any other worksheet. You can then highlight a specific rule and click the Delete Rule button Extending the Reach of Conditional Formats To copy a conditional format: You can select a cell with the appropriate rule and then press Ctrl + C to copy it. Then you select the new range and select Home, Clipboard, Paste, Paste Special, Formats, OK to copy the conditional formatting from the one cell to the entire range. You can select a Home, Styles, Conditional Formatting, Manage Rules. Then you select a rule. In the Applies To column you see the list of cells that have this rule. You can type a new range there or use the collapse button to make the dialog smaller so that you can highlight the new range. When you are using conditional formats that compare one cell to the entire range, using the second method is safer to ensure that Excel understands your intention. Special Considerations for Pivot Tables Refer to Ch. 23 to review the detailed discussion of pivot tables A typical pivot table might contain two or more levels of summary data. To set up a data bar for the detail items in a pivot table, follow the steps: 1. Select a detail cell in the pivot table 2. From the Home tab, select Conditional Formatting, New Rule. The New Formatting Rule dialog appears 3. Because your selection is inside a pivot table, you have new options at the top of the New Formatting Rule dialog. a. Selected Cells-you can apply the rule to many cells. b. All cells showing "Sum of Sales" value-apply the rules including the total column, grand total row, and all the subtotal rows. Remember that the size of the grand total causes all the detail items to have data bars that are too small. c. All cells showing "Sum of Sales" values for "Customer" and "Product"-the meaning of this option is dependent on careful selection of a detail cell in step 1. If you selected a subtotal row instead, this option would apply the data bars only to the subtotal rows. For successful pivot table formatting, click the third option. 4. Define the data bar as usual in the New Formatting Rule dialog. Excel in Practice: Showing Data Bars in Two Colors In the VBA editor, you want to different the values above or below a certain numeric value by color in the data bars. To do this: Select the range of cells to be formatted Use the conditional formatting quick options to add to the range a data bar that is red Select Conditional Formatting, Add New Rule to add a second rule that applies a green data bar. You can see only the most recent rule, so all the data bars are green. Note in the Name box which cell is the active cell. You will need this information in step 7 Press Alt + F11 to switch to the VBA editor Type Selection. FormatConditions(1)Formula="=if(A2->98,TRUE,FALSE)" and then press Enter. Cell A2 should be changed to the name of the active cell from step 4. The result is that the green bars are visible only when the value is 90 or above. In all other cases, the bar appear red.

Chapter 35: Using Pictures and Clip Art

Using Pictures on Worksheets To insert a picture: Select the cell where you want to upper-left corner of the picture be positioned Insert tab, select Illustrations, Picture Browse to the folder that contains your pictures Valid types of pictures include JPG, GIF, PNG, BMP and many others. Click Insert. The new Picture Tools Format tab appears on the ribbon A. Formatting with Picture Styles The Picture Styles gallery offers presets for shadow, rotation, frame and shape B. Resizing and Cropping Pictures Zoom out after inserting the image Resize the image Format tab, Picture Tools section, Size group, find Height and Width spin buttons or the corner To crop the image: Picture Tools Format tab, click the Crop icon, Crop to Shape flyout menu To start with a shape and then fill the shape with the photo, follow the steps: Select Insert, Shape Use green rotation handle to rotate the shape On the Drawing Tools Format tab, select Shape Fill, Picture, Choose a picture Press Ctrl + 1 to access the format dialog for the shape Select the Fill category from the left navigation of the Format Shape dialog At the bottom of the dialog, clear Rotate with Shape. C. Reducing a Picture's File Size Reduce the size of the workbook by reducing the size of the picture: Select the picture In the Picture Tools Format tab, select Compress Pictures from the Adjust group, Compress Pictures dialog, click the Options button D. Adjusting a Picture Picture Tools Format tab, Adjust group for the Sharpness, Brightness, Contrast, Color, and Visual Effects. Correction flyout menu: Picture Corrections Option menu for the Format Picture dialog Color flyout menu Artisitic Effects flyout menu E. Adding Borders Picture Tools Format tab, Border menu Other menus allows to add a shadow, reflection, glow or bevel For more effect, click the effects' drop down menus for the Format Picture dialog. F. Removing the Background Select an image, Picture Tools Format menu, select the Background Removal Adjust the bounding box around the subject Mark to Include to remove color from a section Mark to Remove to keep portions of the image Click Close Background Removal Tool Type in the cells behind the transparent portions of the photo G. Arranging Pictures Offers a rotation tool at Picture Tools Format tab, Arrange group Bring Forward and Send Background potions for overlapping images The Align option allows to snap the pictures to a grid or to make sure that several images line up with each other. To use the latter feature, select the image that was placed last To move the images as a group relative to each other, select the multiple images and group them by using the Group drop-down H. Displaying the Selection Pane For the visibility plane of all images on a worksheet, Picture Tools Format tab, Arrange, Selection Pane, Select the checkbox to toggle with the visibility I. Adding Captions to Images To embed a single selected photograph into a SmartArt diagram for a caption: Picture Tools Format tab, Picture Layout menu To create the SmartArt: Select a single image on the worksheet, Picture Tools Format tab, Picture Layout tab, use the Live Preview, SmarArt Layouts for variations, text, pictures. Click the desired layout. Inserting Screen Clippings To grab an image of a Web page, a PDF file, or a PowerPoint slide, you grab a screen capture of the entire window or a portion of the window. To insert the entire window: Open the document in the other program like Acrobat Reader View the image on the document Switch back to Excel Point to the top left corner where the image will be inserted Insert tab, open the Screenshot drop-down, click an image to insert a picture of that window in the current Excel window To insert a portion of an open application window as a screenshot: Positon the cell pointer where you would like to insert the image Switch to the other application. The screen clipping tool always reverts back to the window used most recently before you switch back to Excel Switch back to Excel Select Insert, Screenshot, Screen Clipping. Draw a rectangular around the portion of the application window you want to capture with the mouse Using Clip Art To add clip art to a worksheet: Postion the cell pointer near where you want the clip art to be inserted Select Insert, Clip Art Pane Search For box and press Go Include results from the Office Online? If yes, then a gallery will be displayed. Select Insert to add the clip art Resize The Clip Art context menus: Copy Delete from Clip Organizer - to delete any local copy of the clip art Make Available Offline - to copy the clip art from the Clips Online to your computer Move to Collection - to organize the clip art on your computer Edit Keywords - to add your own keywords to clip art like tagging in Flickr.com Find Similar Style - to narrow the results to similar images Preview/Properties - to display the exact size of the clip art before you import it

Chapter 33: Using Sparklines

Beautiful Evidence Fitting a Chart into the Size of a Cell with Sparklines Offers line charts, column charts Win/loss chart-there are two rows indicating the teams, in the rows you have markers indicating for each winning game per team. Sparkline-this is a line indicating the trend of objects (this is a group). The sparklines can exist as a single cell or as a group (if the sparklines should have the same scale or be independent). The sparkline features offers the capability to mark the high point, low point, first and last points, and/or all negative points. There is no built in way to label sparklines. The sparklines can be drawn on special formatting layer to accommodate the data visualizations. The layer is transparent, thus the label can be behind the sparkline. Sparkcolumns-the minimum and maximum values for each city are marked in a contrasting color Understanding How Excel Maps Data to Sparklines Excel can create a sparkline with a range of data for example 4 [Row (x-axis)] X 10 [Columns (y-axis)] and the combination for a row [1x4] and a column [4x1]. The sparklines can be plotted by either a row or a column. When you have a perfect square, Excel may create the sparkline along with the wrong axis. To fix this, go to the Sparkline Tools Design tab, open Edit Data drop-down and select Switch Row & Column to reverse the row and column. The sparkline will be plotted like values in each row or column. You can have multiple sparklines in one cell. A. Creating a Group of Sparklines To create a group of sparklines: Create a blank column after column A. Select the data without any headers in this selection On the Insert tab, select Column from the Sparkline Group, displays the Create Sparklines dialog. The default Sparkline has no markers and is auto fitted in a cell, thus you can add a marker to indicate a high point in the cell with the Show group. B. Built-In Choices for Customizing Sparklines The Sparkline Tools Design tab offers several built-in choices. The Edit Data group allows you to respecify the data range for the source data and the location The Type group allows you to switch between Line, Column, and Win/Loss charts The Show group offers the second most useful setting, which is the 6 different markers. Sparklines-you can edit with the Marker Color drop down in the Show group Sparkcolumns-the markers are always shown for "All Points" so this check box is grayed out, other than this will be in color. Win/Loss-choose Markers and Negative. The losses will show a contrasting color. Sparkline Color drop down controls the color of the sparkline Marker Color drop down to control the color of the points as well as the default color for regular markers The Group group to ungroup the grouped sparklines whenever you need to mark the high point and the low points, and you can also clear the sparklines. Any changes you may have applied will create any changes as a grouped sparkline. C. Controlling Axis Values for Sparklines A group of spark columns in a cell with its own scale. The Sparkline Tools Design tab, Axis drop down, Same for All Sparklines will have the same vertical axis if you changed the minimum or maximum. D. Setting Up Win/ Loss Sparklines 1(for positive number), -1 (for a negative number), 0 (to have no marker). If you have a score of 2 in your win/loss sparkline then the marker will appear as 1 for a positive number. You can merge cells to show a larger sparkline You can stretch a sparkcolumn or win/loss chart out wide enough Differentiate by color with the mark points E. Showing Detail by Enlarging the Sparkline and Adding Labels The sparkline fills an individual cell or a range of merged cells You can adjust the height of the cell by the Home, Format, AutoFit Row Height Customize the min and max values (concatenates) based on your data set Wrap text To add labels in a cell: Temporarily change the points Increase the zoom Draw a rectangular Drawing Tools Format tab Under Shape Fill, select More Fill Colors for the transparency of the range of selected values Use the resize handles to accurately view the sparkline with the potted lines and labels Add the max and min values. Then copy the boxes to the other cells. F. Other Sparkline Options To handle gaps in the sparkline, please start following the instruction in the below. This occurs of missing data. Select Sparkline Tools Design Edit Data, Hidden and Empty Cells to display the Hidden and Empty Cell Setting dialog. For any missing data, you can enter the values as 0 which will show a center chart for this value in a sparkline. Or you can have Excel connect the dots by showing the value as a straight line at the very bottom. Any hidden rows and columns will be removed from the sparkline. To reflect this in the chart, select the Show Data in Hidden Rows and Columns checkbox

Chapter 34: Using SmartArt, Shapes, WordArt, and Text Boxes

SmartArt-a collection of similar shapes, arranged to imply a process, groups or a hierarchy. You can add a logo to the shapes Shapes-The shapes can contain text. The text will come from a cell. There are formatting properties. WordArt-stylized text Textboxes-all text to flow in a defined area. Using SmartArt Represents a related step, concept, idea or grouping You can automatically resize the text in all shapes to allow the longest text to fit in the shape. Quick styles allows the user to consistently format the SmartArt diagram. SmartArt styles: Basic Process-basic, filled in text boxes with level 1 text Accent Process- you have filled in text boxes with arrows with level 1 text and in another text box you have your level 2 text box Picture Accent Process- you have a background photo then in another text box you have the fill in color with the level 1 and 2 text Picture Accent List- almost the same as the picture accent process without arrows and the labels are located outside of the text boxes. A. Elements Common in Most SmartArt Headline, body copy, graphic B. Tour of the SmartArt Categories List- Nonsequential list of information Process- designed to show a sequential list of steps Cycle-designed to show a series of steps that repeat Hierarchy- to show the relationships in organizational charts, decision trees and the such. Relationship-to show the relationship between items Matrix-to show four quadrants of a list Pyramid-to show containment, overlapping, proportional or interconnected relationships Picture-can be appropriate with little or no text C. Inserting SmartArt Select a cell in a blank section of the workbook Insert tab, Illustration group, SmartArt icon, Choose a SmartArt Graphic dialog appears D. Changing Existing SmartArt to a New Style Must left click the SmartArt and then do the following Design tab, Layouts, SmartArt Tools You will have a Live Preview of the diagram that you selected and have a list of layouts tailored to the current SmartArt. E. Micromanaging SmartArt Elements Design tab-to change the entire design of the SmartArt. You can make this uniform with the Reset Graphic icon Format tab-Override some aspect of one shape a. Changing Text Formatting in One Element Manually override the text size from the Automatic Mode in all shapes b. Changing One Shape Rotate, nudge and resize shapes Right click the shape and select Format Shape to have complete control of fill, outline, text, effects F. Controlling SmartArt Shapes from the Text Pane In a text pane, you can spell check or have bullet points. The following rules apply to the text pane for SmartArt: Navigate with the arrow keys Insert a new line with the Enter key The Tab key will demote Level 1 text to Level 2 text Press Shift + Tab to promote Level 2 text to Level 1 text Press the Backspace key on an empty line to delete the line Press the Delete at the end of any line to combine text from the next lien with this line Press the End key to move to the end of the current line Press Home to move to the beginning of the current line You can manually resize a shape from the SmartArt You can default the number of shapes from the SmartArt by: Press Tab to demote the item Shift + Tab to promote an item You can also add text in the left column (text pane) for every new shape you would like to insert or remove. G. Adding Images to SmartArt The images are emphasized, the focus shifts to the text, and the picture is an accent. You can insert a picture with the level text 1 once you click inside the box the picture icon will appear. Format the picture with the Picture Tools Format tab. H. Special Considerations for Organizational Chars and Hierarchical SmartArt Hierarchical SmartArt can contain more than two text levels with additional boxes with different colors as you resize them to fit all. You can have four styles in the organization chart. To add an extra shape immediately below the selected level, please go to the SmartArt Tools Design tab, Add Shape drop-down, Add Assistant option. To show boxes within a group, navigate to the Design tab, Create Graphic group, Org Chart drop-down. Select the manager for the group Select the appropriate type form the drop-down to affect all direct reports for the manager The assistant box is a text box that lingers from the parent box. Left hanging is boxes that are shown from the left side from the vertical and vise versa Standard is you have boxes coming down horizontally from the parent box. Both hanging is you have boxes that have relationships from the either side of the vertical line I. Using Limited SmartArt Some of the SmartArt styles cannot be expanded: Arrows, gears, funnels, and matrix shapes have certain limitations on the number of shapes, they can contain. If your layout style cannot contain more than the expected text and shape, then they will be loss before changing the style and saving the workbook. J. Deciphering the Labeled Hierarchy Layouts In its inability to create a dynamic SmartArt where the text for a shape is the result of a calculation. However, you can do this with a regular shape by: Build the SmartArt SmartArt Tools Design tab Convert to Shapes icon Link the individual shapes to formula cells Using Shapes to Display Cell Contents AutoShapes have new formatting options To insert a shape to a worksheet: Select a blank area of the worksheet Insert tab, Shapes drop-down then format Cannot perform a calculation nor insert a formula for a text string But can reference a cell for the text to show in a shape A. Working with Shapes To change the shape style, fill, outline, effects and WordArt effects, navigate to the Format tab, Drawing Tools section. You can change the shape with the Insert Shapes dialog and the format with the Format Shape dialog. B. Using the Freedom Shape to Create a Custom Shape Shapes gallery, Freeform line tools, add a shape or a logo, format it with effects To create a custom shape: Insert a picture of a shape as a guide Insert tab, select the Shapes drop-down, Lines section, the last two shapes are Freeform and Scribble, select the Freeform shape, click one corner of your logo and move the mouse to the adjacent corner of the log and shape it with the mouse, use the effects and fill setting for color and style Using WordArt for Interesting Titles and Headlines To use WordArt for a title or headline: Select the blank section Insert tab, WordArt drop-down Then Format tab, Drawing Tools, WordArt Styles, Text Effects, Transform Using Text Boxes to Flow Long Text Passages Select a range to include the sentences and extend the range to the column D Home tab, Editing, Fill, Justify Use the Text Box object if the characters are more than 255 for one column Excel wraps the sentences to fit the current widths of the columns To use a Text Box object to create two columns of text: Insert tab, select Text, Text Box Drag your doc to draw a large text box on the worksheet Formations with the Format Shape dialog and Text Box category Adjust the margins and alignment if desired Click the Columns button, the Columns dialog appears Choose two columns with nonzero spacing between them Excel Troubleshooting: Eliminating Stray Drawing Objects You scroll at the bottom of the worksheet and back up to have all of the object visible