آموختن اکسل ۹۷ تا اکسل ۲۰۰۷ از آقای اکسل ۲۰۰۷
Learn Excel 97 Through Excel 2007 from Mr. Excel 2007

1. ABout The Author

2. Acknowledgments

3. Foreword

4. Find Icons on the Ribbon

5. Go Wide

6. Minimize the Ribbon to Make Excel Feel a Bit More Like Excel 2003

7. The Office Development Team Likes the Artist Formerly Known as Prince

8. The Paste Icon Is Really Two Icons

9. Use Dialog Launchers to Access the Excel 2003 Dialog

10. Make Your Most-Used Icons Always Visible

11. The Alt Keystrokes Still Work in 2007 (If You Type Them Slowly Enough)

12. Use New Keyboard Shortcuts to Access the Ribbon

13. The Blue Question Mark Is Help

14. All Commands Start at the Top (Except for 2 Controls at the Bottom)

15. What Happened to Tools – Customize?

16. What Happened to Tools – Options?

17. Where Are My Macros?

18. Why Do I Have Only 65,536 Rows?

19. Which File Format Should I Use?

20. Share Files with People Who Are Still Using Excel 97 Through Excel 2003

21. Use Live Preview

22. Get Quick Access to Formatting Options Using the Mini Toolbar

23. MIX FORMATTING IN a Single CELL

24. Copy the Characters from a Cell Instead of Copying an Entire Cell

25. I am a Lobbyist Writing Policy Papers for the White House

26. My Manager Wants Me to Create a New Expense Report from Scratch

27. Increase the Number of Documents in the Recent Documents List

28. Keep Favorites in the Recent Documents List

29. I’ve Searched Everywhere. Where Is the Save Workspace Command?

30. Use a Workspace to Remember What Workbooks to Open

31. Close All Open Workbooks

32. Automatically Move the Cell Pointer in a Direction After Entering a Number

33. Return to the First Column After Typing the Last Column

34. Enter Data in a Circle (Or Any Other Pattern)

35. How to See Headings as You Scroll Around a Report

36. How to See Headings and Row Labels as You Scroll Around a Report

37. How to Print Titles at the Top of Each Page

38. Print a Letter at the Top of Page 1 and Repeat Headings at the Top of Each Subsequent Page

39. How to Print Page Numbers at the Bottom of Each Page

40. How to Make a Wide Report Fit to One Page Wide by Many Pages Tall

41. Arrange Windows to See Two or More Open Workbooks

42. Why Is There a “:2” After My Workbook Name in the Title Bar?

43. Have Excel Always Open Certain Workbook(s)

44. Set up Excel Icons to Open a Specific File on Startup

45. Use a Macro to Further Customize Startup

46. Control Settings for Every New Workbook and Worksheet

47. Open a Copy of a Workbook

48. Open a Saved File Whose Name You Cannot Recall

49. Excel 2007’S Obsession with Security Has Destroyed Linked Workbooks

50. I Navigate by Sliding the Scrollbar and Now the Slider Has Become Tiny

51. Send an Excel File as an Attachment

52. Save Excel Data as a Text File

53. Use a Laser Printer to Have Excel Calculate Faster

54. Use Excel as a Word Processor

55. Add Word to Excel

56. Spell check a Region

57. Translate with Excel

58. Use Hyperlinks to Create an Opening Menu for a Workbook

59. Get Quick Access to Paste Values

60. Quickly Copy a Formula to All Rows of Data

61. Enter a Series of Months, Days, or More by Using the Fill Handle

62. Have the Fill Handle Fill Your List of Part Numbers

63. Quickly Turn a Range on Its Side

64. Stop Excel from AutoCorrecting Certain Words

65. Use AutoCorrect to Enable a Shortcut

66. Why Won’t the Track Changes Feature Work in Excel?

67. Copy Cells from One Worksheet to Many Other Worksheets

68. Have Excel Talk to You

69. Enter Special Symbols

70. Find Text Entries

71. What Do All the Triangles Mean?

72. Why Can’t Excel Find a Number?

73. Get Free Excel Help

74. Copy a Formula That Contains Relative References

75. Copy a Formula While Keeping One Reference Fixed

76. Create a Multiplication Table

77. Calculate a Sales Commission

78. Simplify the Entry of Dollar Signs in Formulas

79. Learn R1C1 Referencing to Understand Formula Copying

80. Create Easier-to-Understand Formulas with Named Ranges

81. Use Named Constants to Store Numbers

82. Assign a Name to a Formula

83. Total Without Using a Formula

84. Add Two Columns Without Using Formulas

85. How to Calculate Sales in Excess of a Quota

86. How to Join Two Text Columns

87. Join Text with a Date or Currency

88. How to Sort on One Portion of an Account ID

89. How to Isolate the Center Portion of an Account ID

90. How to Isolate Everything Before a Dash in a Column by Using Functions

91. How to Use Functions to Isolate Everything After a Dash in a Column

92. How to Use Functions to Isolate Everything After the Second Dash in a Column

93. How to Separate a Part Number into Three Columns

94. Combine Intermediate Formulas into a Mega-Formula

95. Change Smith, Jane to Jane Smith

96. Add the Worksheet Name as a Title

97. Avoid #REF! Errors When Deleting Columns

98. Create Random Numbers

99. Create Random Numbers to Sequence a Class of Students

100. Play Dice Games with Excel

101. Create Random Letters

102. Convert Numbers to Text

103. Calculate a Loan Payment

104. Calculate Many Scenarios for Loan Payments

105. Back into an Answer Using Goal Seek

106. Create an Amortization Table

107. Get Help on Any Function While Entering a Formula

108. Discover New Functions Using the fx Button

109. Yes, Formula Autocomplete Is Cool, if You Can Stop Entering the Opening Parentheses

110. Three Methods of Entering Formulas

111. Start a Formula with = or +

112. Use AutoSum to Quickly Enter a Total Formula

113. AutoSum Doesn’t Always Predict My Data Correctly

114. Use the AutoSum Button to Enter Averages, Min, Max, and Count

115. The Count Option of the AutoSum Dropdown Doesn’t Appear to Work

116. Use AutoSum After Filtering

117. Use Table Functionality to Simplify Copying of Formulas

118. Rename Your Tables

119. Use Simple References in a Table

120. Automatically Number a List of Employees

121. Rank Scores

122. Sorting with a Formula

123. Rank a List Without Ties

124. Add Comments to a Formula

125. Calculate a Moving Average

126. Calculate a Trendline Forecast

127. Build a Model to Predict Sales Based on Multiple Regression

128. Use F9 in the Formula Bar to Test a Formula

129. Quick Calculator

130. When Entering a Formula, You Get the Formula Instead of the Result

131. Why Don’t Dates Show as Dates?

132. Handle Long Formulas in the New Excel 2007 Formula Bar

133. Calculate a Percentage of Total

134. Calculate a Running Percentage of Total

135. Use the ^ Sign for Exponents

136. Raise a Number to a Fraction to Find the Square or Third Root

137. Calculate a Growth Rate

138. Find the Area of a Circle

139. Figure Out Lottery Probability

140. Help Your Kids with Their Math

141. Measure the Accuracy of a Sales Forecast

142. Round Prices to the Next Highest $5

143. Round to the Nearest Nickel with MROUND

144. Why Is This Price Showing $27.85000001 Cents?

145. You Change a Cell in Excel but the Formulas Do Not Calculate

146. Use Parentheses to Control the Order of Calculations

147. Before Deleting a Cell, Find out if Other Cells Rely on It

148. Navigate to Each Precedent

149. Calculate a Formula in Slow Motion

150. Which Cells Flow into This Cell?

151. Total Minutes That Exceed an Hour

152. Convert Text to Minutes and Seconds

153. Convert Text to Hours, Minutes, and Seconds

154. Convert Times from H:MM to M:SS

155. Display Monthly Dates

156. Group Dates by Month

157. Calculate the Last Day of the Month

158. Create a Timesheet That Can Total over 24 Hours

159. Can Excel Track Negative Time?

160. What Is the Difference Between Now and Today?

161. Calculate Work Days

162. Convert Units

163. Use Match to Find Which Customers Are in an Existing List

164. Use VLOOKUP to Find Which Customers Are in an Existing List

165. Match Customers Using VLOOKUP

166. Watch for Duplicates When Using VLOOKUP

167. Remove Leading and Trailing Spaces

168. I Don’t Want to Use a Lookup Table to Choose One of Five Choices

169. Fill a Cell with Repeating Characters

170. Match Web Colors with HEX2DEC

171. Switching Columns into Rows Using a Formula

172. Count Records That Match a Criterion

173. Build a Table That Will Count by Criteria

174. Build a Summary Table to Place Employees in Age Brackets

175. Count Records Based on Multiple Conditions

176. Total Revenue from Rows that Match a Criterion

177. Use the Conditional Sum Wizard to Build Conditional Formulas

178. Create a CSE Formula to Build a Super-Formula

179. Learn to Use Boolean Logic Facts to Simplify Logic

180. Replace IF Function with Boolean Logic

181. Test for Two Conditions in a Sum

182. Can the Results of a Formula Be Used in COUNTIF?

183. How to Set up Your Data for Easy Sorting and Subtotals

184. How to Fit a Multiline Heading into One Cell

185. How to Sort Data

186. Sort Days of the Week

187. How to Sort a Report into a Custom Sequence

188. Sort All Red Cells to the Top of a Report

189. Quickly Filter a List to Certain Records

190. Find the Unique Values in a Column

191. Copy Matching Records to a New Worksheet

192. Replace Multiple Filter Criteria with a Single Row of Formulas

193. Add Subtotals to a Data set

194. Use Group & Outline Buttons to Collapse Subtotaled Data

195. Copy Just Totals from Subtotaled Data

196. Enter a Grand Total of Data Manually Subtotaled

197. Why Do Subtotals Come out as Counts?

198. Subtotal Many Columns at Once

199. Add Subtotals Above the Data

200. Add Other Text to the Subtotal Lines

201. Create Subtotals by Product Within Region

202. My Manager Wants the Subtotal Lines in Bold Pink Cambria Font

203. My Manager Wants a Blank Line After Each Subtotal

204. Subtotal One Column and Subaverage Another Column

205. Be Wary

206. Send Error Reports

207. Help Make Excel 2009 Better

208. How to Do 40 Different What-if Analyses Quickly

209. Remove Blank Rows from a Range

210. Remove Blanks from a Range While Keeping the Original Sequence

211. Add a Blank Row Between Every Row of Your Data Set

212. Excel Is Randomly Parsing Pasted Data

213. Increase a Range by Two Percent

214. Use Find to Find an Asterisk

215. Use an Ampersand in a Header

216. Hide Zeros & Other Custom Number Formatting Tricks

217. Use Consolidation to Combine Two Lists

218. Find Total Sales by Customer by Combining Duplicates

219. Create a Summary of Four Lists

220. Number Each Record for a Customer, Starting at 1 for a New Customer

221. Add a Group Number to Each Set of Records That Has a Unique Customer Number

222. Deal with Data in Which Each Record Takes Five Physical Rows

223. Add a Customer Number to Each Detail Record

224. Use a Pivot Table to Summarize Detailed Data

225. Your Manager Wants Your Report Changed

226. Why Does This Look Different from Excel 2003?

227. Move or Change Part of a Pivot Table

228. See Detail Behind One Number in a Pivot Table

229. Update Data Behind a Pivot Table

230. Replace Blanks in a Pivot Table with Zeros

231. Add or Remove Fields from an Existing Pivot Table

232. Summarize Pivot Table Data by Three Measures

233. Collapse and Expand Pivot Fields

234. Manually Re-sequence the Order of Data in a Pivot Table

235. Present a Pivot Table in High-to-Low Order by Revenue

236. Limit a Pivot Report to Show Just the Top 12 Customers

237. Explore the New Filters Available in Excel 2007 Pivot Tables

238. Why Aren’t the Cool New Filters Available in My Pivot Table?

239. Why Can’t Co-Workers with Excel 2003 Use My Pivot Table?

240. Limit a Report to Just One Region

241. Create an Ad-Hoc Reporting Tool

242. Create a Report for Every Customer

243. Create a Unique List of Customers with a Pivot Table

244. Create a Report That Shows Count, Min, Max, Average, Etc.

245. Use Multiple Value Fields as a Column Field

246. Compare Four Ways to Show Two Values Fields in a Pivot Table

247. Specify a Number Format for a Pivot Table Field

248. Group Daily Dates by Month in a Pivot Table

249. Group by Week in a Pivot Table

250. Produce an Order Lead-Time Report

251. Report Revenue Many Ways in a Pivot Table

252. Format Pivot Tables with the Gallery

253. None of the 23,233 Built-In Styles Do What My Manager Asks For

254. Select Parts of a Pivot Table

255. Apply Conditional Formatting to a Pivot Table

256. Suppress Totals in a Pivot Table

257. Eliminate Blanks in the Outline Format of a Pivot Table

258. Use a Pivot Table to Compare Two Lists

259. Calculated Fields in a Pivot Table

260. Add a Calculated Item to Group Items in a Pivot Table

261. Instead of Using Calculated Items Group Text Fields

262. Build a Better Top 10 by Using Group Selection

263. Group Ages into Age Ranges

264. Use a Pivot Table When There Is No Numeric Data

265. Why Does the Pivot Table Field List Dialog Keep Disappearing?

266. Control the Shape of Report Filter Fields

267. Create a Pivot Table from Access Data

268. Whatever Happened to Multiple Consolidation Ranges in Pivot Tables?

269. Quickly Create Charts for Any Customer

270. Use Microsoft Query to Get a Unique Set of Records

271. Use a Trusted Location to Prevent Excel’s Constant Warnings

272. Import a Table from a Web Page into Excel

273. Have Web Data Update Automatically When You Open Workbook

274. Have Web Data Update Automatically Every Two Minutes

275. The Spaces in This Web Data Won’t Go Away

276. Use a Built-in Data Entry Form

277. How Do I Clean Up This Data?

278. Transform Black-and-White Spreadsheets to Color by Using a Table

279. Remove Duplicates

280. Protect Cells That Contain Formulas

281. Change the Look of Your Workbook with Document Themes

282. Add Formatting to Pictures in Excel

283. Create a Chart with One Click

284. Move a Chart from an Embedded Chart to a Chart Sheet

285. Excel Creates a Chart at the Bottom Of My Data; How Can I Move It to the Top?

286. How Can I Nudge a Chart Within the Visible Excel Window?

287. Why Does Excel Add a Legend to a One-Series Chart?

288. Why Do None of the Built-in Chart Layouts Look Good?

289. The Chart Styles Are Cool, But Why So Few Colors?

290. Display an Axis in Millions Using the Layout Tab’s Built-in Menus

291. Display an Axis in Trillions Using the More Options Choice

292. Customize Anything on a Chart by Right-Clicking

293. The Format Dialog Box Offers a New Trick

294. Charts Acting Flaky? It’s Not Just You

295. Minimize Overlap of Pie Labels by Rotating the Chart

296. Add New Data to a Chart

297. Add a Trendline to a Chart

298. Chart Two Series with Differing Orders of Magnitude

299. Use Meaningful Chart Titles

300. Move the Legend to the Left or Top

301. Avoid 3-D Chart Types

302. Save Your Chart Settings as a Template

303. Other Charting Notes

304. For Each Cell in Column A, Have Three Rows in Column B

305. Copy Formatting to a New Range

306. Copy Without Changing Borders

307. Group Columns Instead of Hiding Them

308. Move Columns by Sorting Left to Right

309. Move Columns Using Insert Cut Cells

310. Move Rows or Columns with Shift Drag

311. Change All Red Font Cells to Blue Font

312. Use Cell Styles to Change Formats

313. Leave Helpful Notes with Cell Comments

314. Change the Appearance of Cell Comments

315. Force Certain Comments to Be Always Visible to Provide a Help System to Users of Your Spreadsheet

316. Control How Your Name Appears in Comments

317. Change the Comment Shape to a Star

318. Add a Pop-up Picture of an Item in a Cell

319. Add a Pop-up Picture to Multiple Cells

320. Draw an Arrow to Visually Illustrate That Two Cells Are Connected

321. Circle a Cell on Your Worksheet

322. Draw Perfect Circles

323. Create Dozens of Lightning Bolts

324. Rotate a Shape

325. Alter the Key Inflection Point in a Shape

326. Make a Logo into a Shape

327. Use the Scribble Tool

328. Add Text to Any Closed Shape

329. Place Cell Contents in a Shape

330. Add Connectors to Join Shapes

331. Draw Business Diagrams with Excel

332. Choose the Right Type of SmartArt

333. Use the Text Pane to Build SmartArt

334. Change a SmartArt Layout

335. Finalize a SmartArt Layout Before Adding Pictures

336. Format SmartArt

337. Switch to the Format Tab to Format Individual Shapes

338. Don’t Convert Another Layout to Create an Organization Chart

339. How Do the Labeled Hierarchy SmartArt Graphics Work?

340. How Does Excel Decide How Many Shapes Per Row?

341. Add New SmartArt Layouts

342. Use Cell Values as the Source for SmartArt Content

343. Change the Background of a Worksheet

344. Add a Printable Background to a Worksheet

345. Remove Hyperlinks Automatically Inserted by Excel

346. Change the Width of All Columns with One Command

347. Control Page Numbering in a Multisheet Workbook

348. Use White Text to Hide Data

349. Hide and Unhide Data

350. Temporarily See a Hidden Column Without Unhiding

351. Build Complex Reports Where Columns in Section 1 Don’t Line Up with Section 2

352. Paste a Live Picture of a Cell

353. Monitor Far-off Cells in Excel 2002 and Later Versions

354. Add a Page Break at Each Change in Customer

355. Hide Error Cells When Printing

356. Organize Your Worksheet Tabs with Color

357. Copy Cell Formatting, Including Column Widths

358. Debug Using a Printed Spreadsheet

359. Copied Formula Has Strange Borders

360. Double Underline a Grand Total

361. Use the Border Tab in Format Cells

362. Fit a Slightly Too-Large Value in a Cell

363. Show Results as Fractions

364. Convert a Table of Numbers to a Visualization

365. Prevent Outliers from Skewing the Visualizations

366. Add Icons to Only the Good Cells

367. Select Every Kid in Lake Wobegon

368. Color All Sales Green for a Day if Total Sales Exceed $1,000

369. Turn Off Wrap Text in Pasted Data

370. Delete All Pictures in Pasted Data

371. Add WordArt to a Worksheet

372. Chart and SmartArt Text Is Automatically WordArt

373. Use MapPoint to Plot Data on a Map

374. Why Does Excel Mark Cells with a Purple Indicator?

375. Add a Dropdown to a Cell

376. Store Lists for Dropdowns on a Hidden Sheet

377. Allow Validation Lists to Automatically Redefine as They Grow

378. Configure Validation to “Ease up”

379. Use Validation to Create Dependent Lists

380. Add a ToolTip to a Cell to Guide the Person Using the Workbook

381. Afterword

382. The Print Version of This Book Is Heavy